editing a lookup field on the fly

K

KLaw

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"?
 
S

Sprinks

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
 
K

KLaw

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

Sprinks said:
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

KLaw said:
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"?
Click to expand...
Click to expand...
 
K

KLaw

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.)
 
A

ajgagne1

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


Sprinks said:
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

KLaw said:
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"?
Click to expand...
Click to expand...
 
Top