Parameter query in VBA

[Jengminchen]

Hi Everyone,

I have a customer forms with a subform containing details of videos being hired. Each time the user click on the video combo box and select a movie, I want it to run a query in the background to see if the video has been previously hired by this customer and if so to pop up a message box with the date it was rented, else continue. I try to do it using a macro but it look clumsy and also always prompt me for the criteria. I have try creating the query and then capture the raw sql code and paste it into the VBA module without success. I am only new to VBA and any help would be much appreciated as I am going nuts!

 

[Rich]

DLookup or DCount is probably easier just see if the VidID has been listed to that customer before

 

[R. Hicks]

Domain Aggregate functions can be slow in returning the needed result if the table contains many records.

My choice would be the "FindFirst" method.

RDH

 

[Jengminchen]

Thanks to Rich and Hicks for prompt reply.

However I am still stuck. Forget to mention that the required query is based on three link tables and two criteria one from customer Id on main form and product Id on subform. Is it still possible and how can I write the code in VBA. Can someone please sent me a sample code to give me an idea so as I can modify it to suit. Please help as I am not making any progress!

 

[Rich]

Is the CustId the parent or child

 

[Jengminchen]

Hi Rich,

The CustId is in the main form while the ProductId in the subform. I intend to place the code in the After Update properties of the ProductId combo box in subform.

The relationship between the tables are:

One customer may order one or many rentals and each rentals must have one or more products. Table: Customer, Rentals and RentalDetails. Thus the query need to get the criteria of CustomerId (Textbox) from Mainform and ProductId (ComboBox) in the subform. Thanks Rich and hope you can help me out with the code. I am sure many users in the forum will benefit greatly from your input.

 

[Rich]

This article should help
Article ID: Q136123

 

[Jengminchen]

Thanks for the directing me to the article. Much appreciated and hope to make some progress soon.