Auto-refresh Lookup Table?

[DanCCC]

Hey all,

My form has a combo box referring to a LU (lookup) table.

My (well-trained) users may have occasion to add a record to the LU table, so I created an "add records" button that opens a little tabular form where they can add (but not edit) a record.

When they close that form and return to the main form, I'd like the new data to be immediately reflected in the combo box, but it's not. I added a "docmd.Requery" item in the OnActivate property of the form, but it did not work. I think "Refresh" is what I really want, but it's not available.

How can I accomplish this seemingly simple objective?

Thanks in advance,

Dan

 

[FancyPrairie]

You need to requery the combobox. For example, cboLU.requery.

 

[Junior1544]

that add form you mentioned.. is it only called from the mail form?? junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[DanCCC]

Bingo! It worked instantly.

The gaps in my Access programming training are showing up clearly here.

Thanks so much, FancyPrairie!

Dan

 

[JeremyNYC]

Dan,

Here's code that makes this a little easier. This assumes that you only need to add one field to the lookup table.

The first bit of code goes in the notinlist event of the combobox. You'll have to set the limittolist property of this combobox to true. You'll have to change the first two parameters to match your table and field and take out the "call errortrap" line, as that calls my standard error handler.

Private Sub cmbBank_NotInList(NewData As String, Response As Integer)
'(c)Copyright 2/6/01 Jeremy Wallace, AlphaBet City Dataworks, jeremy@ymerej.com
On Error GoTo Error
Response = AddListItem("tblBank", "BankName", NewData)
Exit Sub
Error:
call ErrorTrap (Err.Number, Err.Description, "cmbBank NotInList", "frmAccount&quot
End Sub

=======
This next bit goes in a standard module. Make sure you watch out for line wrap and take out the "call errortrap" line.

Public Function AddListItem(sTblNm As String, sFieldName As String, sNewData As String) _
As Integer
'(c)Copyright 2/6/01 Jeremy Wallace, AlphaBet City Dataworks, jeremy@ymerej.com
On Error GoTo Error
Dim sSql As String
Dim lngID As Long

If vbYes = MsgBox("This does not match any existing entries. Do you want to permanently" _
& " add a new entry?", vbQuestion + vbYesNo, "New Data&quot Then
sSql = "INSERT INTO " & sTblNm & " (" & sFieldName & &quot VALUES ('" & Ver(sNewData) _
& "')"
db.Execute sSql, dbFailOnError
AddListItem = acDataErrAdded
Else
AddListItem = acDataErrContinue
End If
Exit Function
Error:
Select Case Err.Number
Case 3022 ' dupe
MsgBox "This name is already in the drop-down list.", vbOKOnly + vbInformation, _
"Data Conflict"
Case Else
ErrorTrap Err.Number, Err.Description, "AddListItem"
End Select
End Function

==========

Note: this has my copyright in it, but I definitely got the idea for how to do this from someone on cdma, though I can't now remember who.

Hope this helps.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[DanCCC]

Jeremy,

Wow, that's pretty cool - I might try that (but then again, given that working on this DB is not even really my job, I may just leave it simple and clunky).

Thanks for sharing your knowledge.

Dan

 

[FancyPrairie]

I do basically the same thing as JeremyNYC, with the following additions.

In the call to AddListItem, I added an Optional argument, which is the name of the form that needs to be launched to fill in the rest of the information required by the LookUp table. If the lookup table requires only 1 field to complete the definition, then JeremyNYC's example works great and the optional form name does not need to be passed to the routine. But if the lookup table requires more than 1 field to complete the definition, then I pass the optional form name argument. That form is then opened to allow the user to complete the definition. The OpenArgs argument of the OpenForm method contains the control name of the form that will be populated with the value entered into the combobox. That way I can update some of the fields on the form with the information entered into the combobox.

However, prior to updating the table or opening a form to update the table, I check to see if the current user has permissions to write to the table. If not, he/she gets the standard error message of not in list, else the user is told that the item doesn't exist, do they want to add it.

Finally, you need a global variable to tell you whether or not the user acually entered something into the lookup form or did they cancel making changes to it (thus no new item added to the combobox).

 

[JeremyNYC]

Fancy,

Fancy, and nice.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.