Requery Method

[FoxProProgrammer]

I am developing a form with a combo box and several text boxes. The name of the form is ManufDetails. The combo box contains a list of manufacturer's names and the text boxes contain the manufacturer's address, city, state, zip, POC, etc. When the user selects a manufacturer from the combo box, I want the contact information in the text boxes to update.

The form's Record Source is set to a Query. It is a long string, but the gist of it is shown below.

SELECT Manufacturers.street, Manufacturers.city,Manufacturers.state,...FROM....WHERE Manufacturers.manufacturer = forms!formname!controlname.value

controlname in the WHERE clause is the name of the combo box.

The On Change event procedure for the combo box contains the statement:

Forms!ManufDetails.Requery

I thought that this would execute the form's Query. Well, the text boxes on the form don't update when the program runs. I ran it in debug mode to make sure that the On Change procedure runs and the Requery statement executes. No problem there.

Here's a fundamental question that might help me figure this out. The text boxes on the form are bound. How do I know if they are bound to the table or the query? I assume that since the form's Record Source is a Query, the forms controls are bound to that Query and not to the underlying table. Also, how can I see the data that comes back from a Query? This might help me troubleshoot the problem. I created a watch variable for "Recordset" in the VB debugger. Is the data that I'm looking for in this object? I couldn't find it, but there are a lot of things in that object.

I have another form that has a similar problem, although it has more than one combo box that must be updated based on the contents of another combo box. Hopefully I can get it to work after I figure out what's wrong with this on one.

BTW, I did get this to work using a different method as shown below:

Sub ManufName_Change()
dim strManufName as String
dim strQuery as String

strManufName = ManufName.Value
strQuery = "SELECT Manufacturers.Street, Manufacturers.City, ... FROM ...INNER JOIN.... WHERE (Manufacturers.Manufacturer) = ' " & strManufName & " ' ORDER BY...
RecordSource = strQuery

When I run this code, the text boxes update correctly. I understand that changing the record source of an open form causes a requery. That explains why this code works, but it doesn't explain why the Requery method doesn't work. I think the Requery code is cleaner because there's no reason to change the record source every time the user clicks in the combo box. The SELECT statement is constant. Only the contents of the variable in the WHERE clause changes.

Thanks for any help.

dz

 

[dalchri]

Actually, you do have to requery the database each time the combo box changes because your RecordSource does change. I know what you mean about the SELECT statement not changing but because your are building the string RecordSource from the variable, you do need to rebuild the RecordSource every time the variable changes.

Perhaps a different approach would suit you better... How about fetching all of the Manufacturers at once, un filtered? Instead of Requerying, just do a FindFirst on your data container. I don't know if you are using the VB Data control, an ADO recordset, or whatever. However, all of those data containers provide the ability to search for a record and change the current position to that record. Because your form controls are bound, they will automatically update to the new values.

 

[FoxProProgrammer]

Thanks for the reply. I understand that I have to requery the database when the combo box changes. I just didn't understand why the Requery Method didn't do the trick. Maybe Access doesn't allow a variable in the WHERE clause of the query. Do you know if this is the case? It works if I build a query string that contains the actual value of the combo box instead of a pointer to the value of the combo box (ManufName.Value). Thanks for suggesting FindFirst. I'll look at some examples.

Best,

dz

 

[Henaniy]

I would trigger any requerying other controls on a form in the AfterUpdate event.

The bound controls are coming from the forms record source.

 

[IrishJim]

I just saw an example of this yesterday in the "Solutions.mdb" Access Sample database.
It was in the "LimitListContents" form.
Jim

 

[IrishJim]

Sorry - it was the "Solution.mdb". - Jim

 

[FoxProProgrammer]

Thanks, Jim. I'll look for that example.

 

[vistor]

IrishJim,

Where is the "Solution.mdb" Access Sample database located?

 

[thekon]

cdwd,

Try:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/bapp2000/html/mdbdownload.asp