FindFirst Method

[FoxProProgrammer]

I am attempting to use the FindFirst Method on a Recordset object that was created with a Query in the Recordsource property of a form. Table A has a one to many relation to Table B, and Table B has a one to many relation to Table C. The Query joins the three tables. Here's my code:

dim strPN as String
dim strManufName as String
dim strSearch as String

strPN = partNbr.Value
strManufName = ManufName.Value
strSearch = "[Manufacturer] = " & strManufName & " AND [Part Number] = " & strPN

RecordsetClone.FindFirst strSearch

When I run the program, I get the following error:

The Microsoft Jet database engine does not recognize 'XXXX' as a valid field name or expression.

I should note that 'XXXX' is the name of the manufacturer selected in the combo box named "ManufName".

Does anyone know why the data that I am looking for is being treated as a field name, or is it barfing on the expression? [Manufacturer] and [Part Number] are valid fields in the Query.

Thanks!

dz

 

[RobertT687]

String variables must be enclosed in quotes when explicitly building SQL statements. Try this :

strPN = partNbr.Value
strManufName = ManufName.Value
strSearch = "[Manufacturer] = '" & strManufName & "' AND [Part Number] = '" & strPN & "'"

Note the imbedded single quotes around strManufName and strPN.

 

[FoxProProgrammer]

Thanks, Robert. It worked!

Unfortunately it lead to another problem though. When I run the program I get an error that says The Microsoft Jet database does not recognize 'Manufacturer' as a valid field name or expression. I checked the Form's Recordsource Query to make sure that Manufacturer was in there, and it is. Any idea why it can't find that field in the query?

dz

 

[RobertT687]

Without seeing the query or table design, its hard to say. If a column [Manufacturer] exists in more than one table, you should add the table qualifier to the search string [tablename]![Manufacturer].

 

[FoxProProgrammer]

Ok, you asked for it! lol

Here's the query:

SELECT Manufacturers.Manufacturer, Manufacturers.Street, Manufacturers.City, Manufacturers.State, Manufacturers.Zip, Manufacturers.Phone, Manufacturers.Extension, Manufacturers.Email, Manufacturers.POC, TRU.[Part Number], TRU.Description, TRU.Acronym, [Serial Number].[Serial Number], [Serial Number].[US Number], [Serial Number].[In Use]
FROM (Manufacturers INNER JOIN TRU ON Manufacturers.[Manufacturer ID] = TRU.[Manufacturer ID]) INNER JOIN [Serial Number] ON TRU.[TRU ID] = [Serial Number].[TRU ID]
ORDER BY TRU.[Part Number], [Serial Number].[Serial Number];

Manufacturer is only in one table. I tried the table qualifier just for grins and it gave me the same error when I ran the program.

Thanks for taking the time to look at this.

dz