Lookup fields update after NotInList

Pangloss14

Registered User.
Local time
Today, 00:11
Joined
May 30, 2003
Messages
19
Hi,

I've spent the last few hours searching through old posts so hopefully this isn't already answered somewhere. Anyhow, I have a form whose data source is the table that I want to update. The table consists of 4 fields, ID, First and Last Name, DOB. I have a combo box that looks up the related fields. If the entered ID doesn't exist, then the NotInList event is triggered which loads a subform to enter the required information.

My problem is that upon return to the main form, the new ID is shown in the combo box but the related fields aren't updated when I select the new value in the combo box. I'm guessing that I need to requery or something but I've run into an error saying that I need to save the current field before requerying (error 2118).

Here is the relevant code (Access 2000).

'frmPatient
Private Sub Combo12_NotInList(NewData As String, Response As Integer)
If MsgBox("Would you like to add new patient?", vbYesNo + vbQuestion, _
"Patient not found") = vbYes Then
DoCmd.OpenForm "frmNewPatient", acNormal, , , acAdd, acDialog, NewData
Response = acDataErrAdded
DoCmd.Close acForm, "frmNewPatient"
Else
Response = acDataErrContinue
End If
End Sub

' Form frmNewPatient
Private Sub Form_Load()
Me.APHC = Me.OpenArgs
End Sub

Regards,

John
 
Just because you open a form the code following that line does not stop code following it from running. It runs while the second form is open.

Requery the first form combo box when the second form is closed.
 
llkhoutx said:
Just because you open a form the code following that line does not stop code following it from running. It runs while the second form is open.

Not in this case - when a form is opened with the acDialog constant the code on the first form stops until the second form is closed.
 
For every response there's always an exception.
 
Hi again,

Pangloss14 said:


Here is the relevant code (Access 2000).

'frmPatient
Private Sub Combo12_NotInList(NewData As String, Response As Integer)
If MsgBox("Would you like to add new patient?", vbYesNo + vbQuestion, _
"Patient not found") = vbYes Then
DoCmd.OpenForm "frmNewPatient", acNormal, , , acAdd, acDialog, NewData
Response = acDataErrAdded
DoCmd.Close acForm, "frmNewPatient"
Else
Response = acDataErrContinue
End If
End Sub

' Form frmNewPatient
Private Sub Form_Load()
Me.APHC = Me.OpenArgs
End Sub


I tried to requery the combo box but I kept getting the 2118 error about needing to save the record before requery. Basically, I came to the conclusion that I can't requery the combo box inside the NotInList event. In any case, I made a command button to requery the combo box but the looked up records still don't get updated. I then changed this button to requery the form and it works, however I can't figure out where to put this so it's done automatically (AfterInsert didn't work). Thanks again.

John
 
Bumping thread... I hope this isn't bad etiquette, I'm still stumped.
Thanks. I actually found that by I can get what I want by requerying the form, but I haven't managed to get this done automatically. I created a button with form.requery to do this. I tried putting the code in several different events to do this but no success as of yet.

Regards,

John
 
Try this

Pangloss14, Sorry I didn't rply back to you on my posting with the similar problem. I have been working on another issue and didn't get the chance to reply back to you. Anyway, the way I got it to work was to put the following into the Afterupdate of the popup form Forms![1stFormName]!ComboBoxName.Requery So, this is requerying the combo box before you go back to the original form. When I tried to code the requery in the 1st form, it was erroring out on the requery. I removed all instances of the requery in the original (1st) form. See this thread for the other code that I used in the 1st form.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=48212&highlight=requery
 
Not On List

Hi

I have attached a sample db using the Not On List routine.

Hope this helps


Lou
 

Attachments

Hi,

Beef and DblDogDare, thanks for your input but I think my problem is slightly different. I'm not actually having any trouble getting the combo box itself to update with the new value. My problem is that the combo box in question is used to look up other values.

For example, if the ID I enter is not found, I show a message asking whether the user wants to add the ID. If yes, then I pop up a form with the entered ID as well as last name, first name and DOB. This info gets entered into the database but I haven't figured out a way to update the original form properly.

After all the information on the pop up form is entered, the original form's combo box has been updated to reflect the new value, but when I select the new value, the associated information isn't shown. Currently, I have a command button to update the form which works but I want this to happen without intervention. Thanks.

BTW, DblDogDare, I found that with the NotInList event, I didn't need to requery the combo box in order to get it to update. Anyone?

John
 
Last edited:
Hello,

I'm still stuck. Maybe I'm dumb:confused: , anyone have an idea? Thanks.

John
 
Rather than using code to set the other field values, base the form on a query that joins to the lookup table with a left join. As soon as a value is selected in the combo, Access will automatically populate the other fields (make sure that you change their controlsources because the data they need will be in the form's recordsource once you change it to include the lookup table and select the necessary fields).

Select O.OrderID, O.OrderDate, O.CustomerID, C.CustomerName
From OrderTable as O Left Join CustomerTable as C On O.CustomerID = C.CustomerID;

By using a query like this, the customercombo on your form will autonmatically populate the name field as soon as a customerID is entered. It works if you just open the query except you won't have a combo to select customerID, you'll need to type it in. Access help calls this an Autolookup query and it is the heart and soul of relational databases. Make it your friend and save yourself some coding as well as making your app more eficient.
 
Hi,

Thanks for the input but I'm not quite sure I follow completely. Would you happen to have a sample DB handy?

Thanks.
 
In the example form, when you choose an item from the type combo and tab out of the field, the typedesc field automatically fills with the value from the lookup table.
 

Attachments

Hi Pat (and others),

I think that either you don't understand the issue that I'm having or I don't understand your solution. All the fields in question are part of the same table so a query doesn't change anything in this case. I'm not trying to populate fields using code, I just need the form to refresh after the NotInList event so that the underlying records are updated.

Table 1: ID (PK), Name, DOB
Form 1 based on table 1:
Combo Box ID: Looks up record based on ID.
Text Box Name: Name (comes up automatically)
Text Box DOB: DOB (comes up automatically)

My issue: If I enter a new value in the Combo Box, the NotInList event is triggered and a new form loads with prompts to enter Name and DOB for the new ID. Upon return to the main form, the new value is shown in the Combo Box, but the Text Boxes aren't updated until the form is reloaded/refreshed. I need to refresh the form automatically but can't figure out which event will do this. I can manually do this (with a command button) but I can't get it to work without user intervention. Thanks

John
 
Hi all,

I figured it out. Hopefully this will help others. It turns out that the DataEntry flag was the key in this puzzle. I had to add Form.DataEntry = False in the AfterUpdate event of the main form. What I don't understand is why I needed to do this when the form's data entry property was set to false in the design view.

John
 

Users who are viewing this thread

Back
Top Bottom