Need 'entered' value added to list

[p-rat]

I have a combo box that when a value is selected from the list (query)
it autofills an unbound text box with another value in the same row of
the query return. The combo box has for example Employee Names and the
unbound text bound will show the Employee ID Number.

I have the combo box set to 'limit to list'=No. The data entry clerk
can add a value that is not on the list and that is fine. How do I
have the unbound text box require the clerk to enter in the Employee
ID Number and how do I get these two values (newly entered Employee
Name and newly entered Employee ID Number) to be added to the query/
table list in the back so the next time the clerk goes into the form
the before mentioned Name/ID Number will be in the drop down?

Thanks for any help.

 

[Jeff Boyce]

Use the LimitToList = Yes!

(then you can use the NotInList event to popup a data entry form to add the
new values and repopulate the combobox).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[p-rat]

Being new to this could you explain how this might be done. I can
create a form, but am not sure of any code to popup the form or to
point the data entered to the combobox table/query. Thanks for your
help.

 

[Jeff Boyce]

Open the form in design view.

Select the combobox and open the Properties window.

Select Limit-to-List and set it to "Yes".

Select the NotInList event and use F1 to get Access HELP about the syntax (a
code example is provided).

Regards

Jeff Boyce
Microsoft Office/Access MVP


Being new to this could you explain how this might be done. I can
create a form, but am not sure of any code to popup the form or to
point the data entered to the combobox table/query. Thanks for your
help.

 

[p-rat]

I created the form and when the item entered isn't in the table list
(OnNotInList) the form opens. Data is entered into two text boxes.
When clicking on command button to close I get this error:

"The item you entered isn't an item in the list. Select an item from
the list, or enter text that matches one of the listed items."

Here are event procedure code from the combobox properties:

Code in the OnNotInList:

DoCmd.OpenForm "LeaseWellRRCNumber", , , , , acDialog

Code in AfterUpdate:

Me.RRC_Number.Value = Me.Lease_Well_No.Column(1)


Also, it seems like it doesn't want to add the text that is entered
into the two text boxes on the form to the table. I'm new and stumped?

 

[Jeff Boyce]

I don't know (yet) what you are using to "feed" the combobox, but it sounds
like you are getting closer.

If you adopt the code you find in HELP for handling the not-in-list event,
it should take care of opening the form, adding the new value, closing the
popup and requerying the combobox. Check Access HELP.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I created the form and when the item entered isn't in the table list
(OnNotInList) the form opens. Data is entered into two text boxes.
When clicking on command button to close I get this error:

"The item you entered isn't an item in the list. Select an item from
the list, or enter text that matches one of the listed items."

Here are event procedure code from the combobox properties:

Code in the OnNotInList:

DoCmd.OpenForm "LeaseWellRRCNumber", , , , , acDialog

Code in AfterUpdate:

Me.RRC_Number.Value = Me.Lease_Well_No.Column(1)


Also, it seems like it doesn't want to add the text that is entered
into the two text boxes on the form to the table. I'm new and stumped?

 

[p-rat]

When I go into the newly created form and enter data into the two text
boxes (one is an Oil Field lease name and one is an Oil Field ID
Number) whose RowSource is to a table with three columns KeyID,
Lease_Well_No, and RRC_Number then "SaveandClose" the form closes and
the data is saved to my table. However, when I go into my form that
the data entry Clerks use and try it the way it should be working is
when I get my errors mentioned previously. So trying to troubleshoot
or get an overall understanding, I guess that the 'new' form works on
its own, but not within an event procedure on the other form?

Did I mention that the combobox 'autofills' an unbound text box on the
main form. I don't know if this matters. The combobox is the
Lease_Well_No and when selected it autofills a textbox with the
RRC_Number. This might be easy when finally figured out, but I can't
see the forest through the trees right now.

 

[p-rat]

Here is what I have in the OnNotInList now:

Private Sub Lease_Well_No_NotInList(NewData As String, Response As
Integer)
Dim sqlAddState As String, UserResponse As Integer
DoCmd.OpenForm "LeaseWellRRCNumber", , , , , acDialog
Beep
UserResponse = MsgBox("Do you want to add this value to the list?",
vbYesNo)
If UserResponse = vbYes Then
sqlAddState = "Insert Into LeaseWellRRCNumber ([LeaseName])
values ('" & NewData & "')"
CurrentDb.Execute sqlAddState, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

The data entered into the newly created form is being saved to the
underlying table and requeries to add to list. The value entered into
the second text box of the newly created form is being saved to the
table, but does not autofill on the form as needed. This is only thing
that is stumping me and the code in the 'afterupdate' event looks to
be correct?

 

[Jeff Boyce]

So at this point, it isn't the part about getting the new value into the
table (and to show up in the combobox), it's about getting the combobox's
AfterUpdate event to trigger to "load" an unbound textbox?

One approach might be to "call" the cboYourCombobox_AfterUpdate() procedure.
Since the focus has moved to the popup form, maybe using the OnActivate
event of the main form would give you a way/place to call it?

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


Here is what I have in the OnNotInList now:

Private Sub Lease_Well_No_NotInList(NewData As String, Response As
Integer)
Dim sqlAddState As String, UserResponse As Integer
DoCmd.OpenForm "LeaseWellRRCNumber", , , , , acDialog
Beep
UserResponse = MsgBox("Do you want to add this value to the list?",
vbYesNo)
If UserResponse = vbYes Then
sqlAddState = "Insert Into LeaseWellRRCNumber ([LeaseName])
values ('" & NewData & "')"
CurrentDb.Execute sqlAddState, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

The data entered into the newly created form is being saved to the
underlying table and requeries to add to list. The value entered into
the second text box of the newly created form is being saved to the
table, but does not autofill on the form as needed. This is only thing
that is stumping me and the code in the 'afterupdate' event looks to
be correct?