Combobox requery (A2K)

Topic

New Reply

Mark Kindly answered part of this question at the begining of August but I had to leave my office for 3 weeks and return to the Uk so am just trying to solve my problem, any help appreciated.

I have a form that has some details of my Customers, in the ‘notonlist’ event I have added one of the self contained Access macros to open my Customers form if there is no record. This works fine but when I return to the original form it does not update (if I close and then re-open the form it is there).

Mark suggested that a do a requery of the combo box using an event procedure me.Customers.requery.

Only ever having used built in functions I cannot get it in my mind how to write the open form part, update new record in another form, return to original form and have it requery the Combo box.

I have no experience whatsoever in making macros, VBA or event procedures but in my feeble mind I Imagine it should go something like

If no matching record then
open frmCustomers
me.Customers.requery

Sounds easy in an ideal world doesn’t it, help please

TIA

Steve

Reply | Quote

Replies

Well, there is no time like the present to start learning VBA.

Instead of the macro, you need to select the Event Procedure for the Not In List Event. You need a line of code to open the customer form, then another line of code to requery. Something like this:

DoCmd.OpenForm “frmCustomer”, , , , , acDialog
Me.cboCustomer.requery

Using the acDialog opens the customer form while suspending the rest of your code. Without it, the requery would take place immediately. In this way, the requery won’t take place until you close frmCustomer. You can check Help on OpenForm to see other options that you might want to use.

Reply | Quote

Thanks once again Mark but this has now given me another problem, the dreaded run time error

Run-time error ‘2118’:
You must save the current field before you run the requery action

On my Customers form I added a save button thinking this would do the trick but it does not. Any help apreciated.

TIA

Steve

How did that song go? “Mama told me there’d be days like this”

Reply | Quote

Oh yeah, I forgot about that. I usually don’t put this code in NotInList event as it is too easy to create a new record (I make it a separate button). You need a “mycombobox.undo” remove the user’s entry, prior to the requery. Then you need a way of getting the new entry into the field. For example, if this is a Customer combobo and the customerID is a sequential autonumber or customer counter, I just go out and use DMax to get highest customer#.

Reply | Quote

Thanks for the quick response Mark but my level of Access is so low it didn’t really mean a thing.

If you have a moment could you explain it a little more detail, if you cannot thanks for the help you have given so far.

Best regards

Steve

Reply | Quote

Are you trying to do this thru a macro or code?

Reply | Quote

Hi Mark,

I am really sorry if I am driving you barking mad with this problem, I have several books and have looked at the MS help but none of it narrows it down to what I am trying to do. I have followed your instructions and now get a run time error that says object required (please see below). As we are at present a 2 man band it doesn’t matter that any user can enter a new Customer as we will always identify them by name not the autonumber. I tried with and without the DMax part but I get the same error.

Option Compare Database

Private Sub CustomerId_NotInList(NewData As String, Response As Integer)
DoCmd.OpenForm “frmCustomer”, , , , , acDialog
cboCustomerId.Undo
cboCustomerId.Requery
End Sub

Private Sub del01_Click()
On Error GoTo Err_del01_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_del01_Click:
Exit Sub

Err_del01_Click:
MsgBox Err.Description
Resume Exit_del01_Click

End Sub

Reply | Quote

Your combobox is apparently named [CustomerID]. I didn’t know it’s name, so I just used [cboCustomerID] in my example. You must change it to match your combobox name.

BTW, it is generally a good idea to rename your bound controls to something other than the underlying fieldname. That’s why I used [cboCustomerID].

Reply | Quote

Eureka, the idiot finally got it

Thanks Mark, I assumed that Access always called a combo box cbo on its own, obviously I need to read more on naming conventions. It works a treat now.

As I can’t get you a beer I will have a nice cold Tiger beer in a sleazy bar in your honour tonight.

If you ever get stuck and need help shipping infectious research samples anywhere, I’m your man!!

Thanks again

Steve

Reply | Quote

>>I will have a nice cold Tiger beer <<

Sounds good, but I'm not familiar with the brand. A local brew?

And I usually don't have too many infectious research samples to ship, but it's always good to know a name in the business!

Reply | Quote

Your DoCmd.OpenForm of your Customer form should be done using acDialog as the Windows Mode option in OpenForm. This suspends execution of the rest of the code in your NotInList event until the customer form is closed. Immediately upon return, you next lines of code should be:

cboCustomerID.undo ‘or whatever your controlname is
cboCustomerID.requery

At this point, you don’t need any more code, and you can just let user reselect from the list.

If you want, you can guess at which customer was just added. This assumes that the CustomerID is an autonumber or other custom counter, such that the customer with the highest ID is the last one entered. So, add this line after the requery above:

cboCustomerID = DMax(“customerid”,”tblCustomers”)

Reply | Quote

A 1000 thanks for your assistance Mark, I am rushing around packing to leave the UK for Singapore tonight so I will not be able to try this until Monday, if you read a report about a psyco smashing computers in that neck of the woods you know I have failed.

Cheers

Steve

Reply | Quote