I need help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form "PurchaseOrders", on the purchase order form I have a cmd
button to take me to another form "ItemList". This is used when an item is
not in my database and I would like to add the item without leaving the
"PurchaseOrder". All is working well except after adding the item to the
"ItemList" this new item is not available in my drop down combo box unless I
close the "PurchaseOrder" and reopen. I have verified that the item is indeed
updated on the "ItemList" but I can't figure out how to refresh the table and
make the new item available in my drop down without closing and reopening.
Any help is greatly appreciated.
 
All is working well except after adding the item to the
"ItemList" this new item is not available in my drop down combo box unless I
close the "PurchaseOrder" and reopen.
Click to expand...

Just requery the combo box.

If you use the NotInList event to add the new record, it will do so
automatically.

John W. Vinson[MVP]
 
Open the form in dialog mode; this pauses code execution in the calling
procedure until the form is closed. Then in the calling procedure requery
the combo box. So the code for the button's Click event procedure would go
like this:

DoCmd.OpenForm "ItemList",DataMode:=acFormAdd, WindowMode:=acDialog
Me.cboItemList.Requery

where cboItemList is the name of the combo box.

If you only have to insert data into one control on the ItemList form, then
you don't need to open the second form at all you can, as John suggests, use
the combo box's NotInList event procedure, which is triggered when you type a
new value into the combo box. Here's an example for adding a new City:

Private Sub cboCities_NotInList(NewData As String, Response As Integer)

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

Set ctrl = Me.ActiveControl
strMessage = "Add new city to list?"

strSQL = "INSERT INTO Cities(City) 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

Set cmd = Nothing

End Sub

However, you can also use the NotInList event procedure where you do have to
open a form. Here's an example for adding a new city again, but this time
for opening a form, in which you can add other data such as the County or
State the city is in:

Private Sub cboCities_NotInList(NewData As String, Response As Integer)

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 "frmCities", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure city has been added
If Not IsNull(DLookup("CityID", "Cities", "City = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Cities table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub

Ken Sheridan
Stafford, England
 
Thanks for the information but can you explain a litter more. I don't know
how to requery a combo box nor use the NotInList event.
 
I'm still not understanding something, I have the following that passes with
any action:

Private Sub cboItemsOrdered_NotInList(NewData As String, Response As Integer)
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 "frmItemList",
DataMode:=acFormAdd,
WindowMode:=acDialog,
OpenArgs:=NewData
'ensure item has been added
If Not IsNull(DLookup("ItemNumber", "Item=""" & NewData & "")) Then
Response = acDataErrAdded
Else
strMessage = NewData & "was not added to Item List."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If

Else
Response = acDataErrContinue
ctrl.Undo
End If



End Sub
 
Ken,
Would you please contact me offline, via my web site Contact, about an access.forms
post (Subject: "Ken Sheridan please contact me") a few weeks ago...
It was supposedly from Eric Sassaman @ Microsoft.
Thank you,

Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
 
Back
Top