Populate a Text Box after running Macro

Topic

New Reply

Please advice.

I am a novice at working with VBA and complicated Access implementation. I am working in Access 2003.

I have created a form that after the user enters the customer number in a text box it runs a macro that updates the ownership code in the table. Every customer number has an associated ownership code, but the user does not know the ownership code, only the customer number. I have a table that has all the customer numbers and the associated ownership codes. Unfortunately, the ownership code does not appear on the form in the text field after the macro has run. The table has the ownership code after the macro runs, but the ownership code does not appear on the form. Is there a way to have the ownership code appear in the text box without closing the form?

Thanks for your help!

Reply | Quote

Replies

Are you working with VBA or are you using a macro? If you are using VBA, you can do either a Refresh or a Requery on the form and it should then display the value. If you are using a Macro, then you can use the Requery Action and specify the control name for the ownership code. If you need further assistance, post your VBA Code or Macro and we’ll try to give you a hand.

Reply | Quote

I attempted to use the Requery Action, but I think I maybe missing something.

Attached is a sample database.

I added the Requery Action to the macro. I was unsure how to assign the control name. When I did not assign a control name it worked for the first entry in the form, but every subsequent entry in the form brought up the first entry.

Thank you for your help.

Reply | Quote

I attach a revised version. This does not use your macro nor the query.

I have changed the textbox where you enter the MBR into a combo box that lists the MBRs, and their corresponding OwnerIDs, then uses the After Update Event of the combo to set the value of the OWNERID to the value in the second column of the combo.
(Column numbers are zero based, so the second column is Column (1))

Reply | Quote

John – Thanks for your help!

Unfortunately, the sample I provided only listed 2 Customers, but the actual database I am working with have over 17,000 customer records and owner IDs, so I think it would be too difficult to have the user work with a combo box.

Please let me know if there are any other suggestions.

Thanks!

Reply | Quote

The alternative is to do a lookup:

Code:

Me.OwnerID = DLookup("OwnerID", "tblOwnershipCode_ShopStatus", "[MBR]= " & Chr(34) & Me.MBR & Chr(34))

but the MBR entered might be a mistake, so better would be

Code:

Me.OwnerID = NZ(DLookup("OwnerID", "tblOwnershipCode_ShopStatus", "[MBR]= " & Chr(34) & Me.MBR & Chr(34)),"Not Found") 

But I suppose the bigger question is why are you duplicating two fields between two tables. In general that is something you should avoid.

Reply | Quote

kgudgel

It does seem that John has set this up the easiest way.

Have you tried it? You can still input the customer number into the combo box. The combo box will find it the listing, and the rest happens from there.

Reply | Quote

Yes. This worked. Thanks for your help, John!

The reason this is needed is because each customer (MBR) has a specific set of zip codes tied to the MBR and each OwnerID can have multiple MBR tied to the OwnerID.

For example:

OwnerID = 1234
MBR = 12-3456AA
Zips = 12345, 12346, 12347
MBR = 12-4578AA
Zips = 12348, 12349, 12350

When the user enter the database they only know the MBR and they want to know all the zip codes under the OwnerID umbrella. I have set up a subform on the form that displays all zips codes (12345, 12346, 12347, 12348, 12349, 12350) based on the OwnerID that is located based on the MBR entered in the form.

But maybe I have went about getting this data the totally wrong way? Not sure.

Reply | Quote