Update a combo box contents in one form after entering data in ano

[David K.]

Is there some kind of event code that would update the contents of an open
form after entering in data and exiting a separate form?

I have a form (frmNCR) that has a combo box which is used to select a part
number from a list. Very often, when entering data into the frmNCR form a
new part number has not been entered yet. I can do this with my
frmPartNumber form but I would really like to be able to click a button I've
created within the frmNCR form.

I've created a button on the frmNCR form that brings up the frmPartNumber
form. I can then enter the new part number and exit the frmPartNumber form.

Is it possible to update the frmNCR form without having to exit and reopen?

Thanks in advance for any help.

 

[Jeanette Cunningham]

Hi David,
In the unload event of frmNCR, put code to requery the combo on the other
open form.
Forms!NameOfForm!NameOfCombo.Requery

Replace the obvious with the actual names.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[David K.]

Thanks, Jeanette.

I'm a rookie and couldn't get what you suggested to work but I was able to
get it to work by putting the code in the On Close event of the Part Number
form. It works great! Thanks!

I'm reading about the On Unload event to see where I went wrong. Take care.

 

[Ken Sheridan]

You can in fact do this transparently by typing the new part number directly
into the combo box on the frmNCR form. This is done by putting code in the
combo box's NotInList event procedure. The code you use depends on whether
you need to add data to other fields in the table of part numbers, e.g.
PartDescription etc. If you don't and you just need to enter the part number
into the table of part numbers, which I'll call PartNumbers for this example,
then you don't need to open the frmPartNumber form at all, in which case the
code for the combo box's NotInList event procedure would be like this:

Dim cmd As ADODB.Command
Dim ctrl As Control
Dim strSQL As String, strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

strSQL = "INSERT INTO [PartNumbers] ([PartNumber]) VALUES(""" & _
NewData & """)"

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
cmd.CommandText = strSQL
cmd.Execute
Response = acDataErrAdded
Else
Response = acDataErrContinue
ctrl.Undo
End If

I've assumed in the above that the part number is a text data type field.
If it’s a number data type amend the code to omit the quotes around the value:

strSQL = "INSERT INTO [PartNumbers] ([PartNumber]) VALUES(" & _
NewData & ")"

If on the other hand you also need to insert other data such as the part
description then the code would be like this to open frmPartNumber and pass
the value you entered in the combo box to it:

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmPartNumber", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmPartNumber closed
DoCmd.Close acForm, "frmPartNumber"
' ensure part number has been added
If Not IsNull(DLookup("PartNumber", "PartNumbers", "PartNumber = """
& _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Part Numbers table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

Again if PartNumber is a number data type rather than text amend the code to:

If Not IsNull(DLookup("PartNumber", "PartNumbers", "PartNumber = " & _
NewData)) Then

You also need to put the following code in frmPartNumber's Open event
procedure to set the DefaultValue property of the PartNumber control to the
value you entered in the combo box and passed to the form:

If Not IsNull(Me.OpenArgs) Then
Me.PartNumber.DefaultValue = """" & Me.OpenArgs & """"
End If

Note that in this case you don't need to amend this code if PartNumber is a
number data type as the DefaultValue property is always a string expression
regardless of the data type of the underlying field.

Ken Sheridan
Stafford, England