Would like to refresh combo bxs, after adding records

[DanEaton]

My database has a main data entry form called frmCatalogDataEntry. On that form are many different command buttons. Two such command buttons are to update info if it is not already in the combo boxes. For example, the user enters the catalogs date, and catalog#, only to find out that the supplier isn't in the combo box for suppliers. I have an "Add New" button beside the combo box that lets them jump to the frmSupplierDataEntry (via a macro that first closes the form). Once this occurs the program displays a 'The field SupplierID can't contain a Null b/c of requ'd property true' msgbox, then a 'you can't save the record at this time' one. If you hit 'ok', of course everything works. But the users will be confused at what's going on and what's being asked. I would prefer if there was a way to update the supplier, then just return without ever closing the frmCatalogDataEntry, but I get the same error messages when I try to add a refresh method into the macro or code. Is there a way around this so that the combo box will be refreshed without getting these error messages...Another compromise that I would settle for, would just to be able to customize (or annihilate) these error messages when the macro closes the frmCatalogDataEntry and opens frmSupplierDataEntry...Thanks Dan Eaton
deaton@caemachinery.com

 

[Darrylles]

Hiya,

This is exactly the reason why I 'bang-on' about using an autonumber ONLY as the primary key for EVERY table.

Your SupplierId is at least part of the primary key.
You don't have the key value, so you want to leave the form to create a new one - Access won't let you because it demands a key before leaving a record.

If the pk was an autonumber, the user never sees it - is not expected to enter it and you could go to another form to add the 'non-key' supplier_id - no prob.

I don't know the efficient or 'elegant' solution, but I would guess at putting a 'dummy' value in the Supplier_id field before you attempt to leave the form. How you would check to make this unique, I know not.
Dependant on the data type of supplier_id - you could possibly put an asterisk in there temporarily - until you come back and populate it with a correct value?

Regards,

Darrylle


"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[hughclaire]

Following codes are from Access 20002 Desktop Desktop Developer's Handbook
by Litwin, Getz, and Gunderloy. (Sybex)

Please read this book to resolve your problem.

' Change this constant to
' False to use ADO.
#Const USEDAO = True

Private Sub cboNotInList_NotInList( _
NewData As String, Response As Integer)
Dim strMsg As String
#If USEDAO Then
Dim rst As DAO.Recordset
Dim db As DAO.Database
#Else
Dim rst As ADODB.Recordset
#End If

strMsg = "'" & NewData & "' is not in the list. "
strMsg = strMsg & "Would you like to add it?"
If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, _
"New Company&quot Then
Response = acDataErrDisplay
Else
#If USEDAO Then
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblCompanies&quot
#Else
Set rst = New ADODB.Recordset
rst.Open _
Source:="tblCompanies", _
ActiveConnection:=CurrentProject.Connection, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic, _
Options:=adCmdTableDirect
#End If
rst.AddNew
rst("Company&quot = NewData
rst.Update
Response = acDataErrAdded
rst.Close
End If
End Sub

 

[DanEaton]

hughclaire, what is this code doing exactly? As I see it, this code will add a new record into the table without the user ever seeing anything (ie. will never jump to the data entry form), I like this idea. I am not proficient in VBA, do I need to use the ADO part of this code? Also, will this code work for the supplier data entry if it is a foreign key to the catalog table which frmCataogDataEntry is based on? Thanks
Dan Eaton
deaton@caemachinery.com