editing a lookup field on the fly

[Guest]

Hi - I have a form with a look-up field that retrieves data from a table. If
a record is not in the list (for example: Contact name), I run a macro in
the OnNotInList which opens an Add Contact Form. When I close that form I
would like to have the original form that I'm on refresh or update so that I
don't keep getting the "Not in List" errors. Where do I put a refresh
command? Or is there some other way to edit a look-up field "on the fly"?

 

[Guest]

Hi, KLaw.

By "look-up field", I'm hoping you mean "Combo Box". The Lookup field
feature in Access is worse than useless. Avoid its use and use combo boxes
on your forms.

To open your form, and have its data added, modify the following code,
adding appropriate error handling, and place it in the OnNotInList event
procedure:

Dim strsql As String, x As Integer
Dim strFrmName As String
Dim strLinkCriteria As String

strFrmName = "YourFormName"
x = MsgBox("Do you want to add this record?", vbYesNo)
If x = vbYes Then
strsql = "Insert Into YourTable ([YourFieldName]) values ('" & NewData &
"')"
CurrentDb.Execute strsql, dbFailOnError
strLinkCriteria = "[YourFieldName] = '" & NewData & "' "
DoCmd.OpenForm strFrmName, , , strLinkCriteria
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

Hope that helps.
Sprinks

 

[Chris Reveille]

Try

Me!MyControl.Requery
after you add the new contact

Chris

 

[Guest]

Yes, I did mean combo Box. The code worked great! Thank you so much!

Hi, KLaw.

By "look-up field", I'm hoping you mean "Combo Box". The Lookup field
feature in Access is worse than useless. Avoid its use and use combo boxes
on your forms.

To open your form, and have its data added, modify the following code,
adding appropriate error handling, and place it in the OnNotInList event
procedure:

Dim strsql As String, x As Integer
Dim strFrmName As String
Dim strLinkCriteria As String

strFrmName = "YourFormName"
x = MsgBox("Do you want to add this record?", vbYesNo)
If x = vbYes Then
strsql = "Insert Into YourTable ([YourFieldName]) values ('" & NewData &
"')"
CurrentDb.Execute strsql, dbFailOnError
strLinkCriteria = "[YourFieldName] = '" & NewData & "' "
DoCmd.OpenForm strFrmName, , , strLinkCriteria
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

Hope that helps.
Sprinks

Hi - I have a form with a look-up field that retrieves data from a table. If
a record is not in the list (for example: Contact name), I run a macro in
the OnNotInList which opens an Add Contact Form. When I close that form I
would like to have the original form that I'm on refresh or update so that I
don't keep getting the "Not in List" errors. Where do I put a refresh
command? Or is there some other way to edit a look-up field "on the fly"?

 

[Guest]

Thank for the help, but I only know enough to get myself in trouble!
Where do I add that line of code? (My macro opens the Contacts Form, I enter
the new record, then close the form. After the form closes, I am still in
the control on the original form, with the new entry still in focus and the
combo box has not updated to reflect the added record, which gives me the not
in list error, again. If I close the form and reopen, the new record is
there - I just don't know where to put the refresh command to avoid having
to do this.)

 

[Guest]

Hi Sprinks,

I tried your code in my database but for some reason It gets me only part
way then I get an error message, "Systax error in INSERT INTO statement.
I've tried many different solutions but none seem to work. Any solution You
suggest, I'll try.

Thanks
--
Frenchy in NH

Hi, KLaw.

By "look-up field", I'm hoping you mean "Combo Box". The Lookup field
feature in Access is worse than useless. Avoid its use and use combo boxes
on your forms.

To open your form, and have its data added, modify the following code,
adding appropriate error handling, and place it in the OnNotInList event
procedure:

Dim strsql As String, x As Integer
Dim strFrmName As String
Dim strLinkCriteria As String

strFrmName = "YourFormName"
x = MsgBox("Do you want to add this record?", vbYesNo)
If x = vbYes Then
strsql = "Insert Into YourTable ([YourFieldName]) values ('" & NewData &
"')"
CurrentDb.Execute strsql, dbFailOnError
strLinkCriteria = "[YourFieldName] = '" & NewData & "' "
DoCmd.OpenForm strFrmName, , , strLinkCriteria
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

Hope that helps.
Sprinks

Hi - I have a form with a look-up field that retrieves data from a table. If
a record is not in the list (for example: Contact name), I run a macro in
the OnNotInList which opens an Add Contact Form. When I close that form I
would like to have the original form that I'm on refresh or update so that I
don't keep getting the "Not in List" errors. Where do I put a refresh
command? Or is there some other way to edit a look-up field "on the fly"?