Help! What's the correct syntax for the Findfirst DAO

[KmhComputer]

I don't know what I am doing here.

I have a recordset and I want to use the Recordset.Findfirst command to find
a record, but I can't seem to get my syntax correct.

I have an integer variable I'll call TempComp.
The Comp field in my table is an integer.

I essentially would like to do the following:

Recordset.Findfirst Comp = TempComp
If Not Recordset.Nomatch Then
Recordset.Field2 = AnotherVariable
Endif

Can anyone please help with this? Your help would be greatly appreciated.

Thanks,
Kathy

 

[Allen Browne]

The argument needs to be a string (so in quotes), and the string needs to
look just like the WHERE clause of a query.

Dim strWhere As String
strWhere = "[Comp] = " & TempComp
Debug.Print strWhere
rs.FindFirst strWhere

If it fails, open the Immediate window (Ctrl+G) and see what the Debug.Print
line dumped there.

 

[KmhComputer]

Hi,

Thanks for getting back to me so quickly.

I added/changed the code as you suggested and I still get an error.

I get the "Operation is not supported for this type of object."

The debug shows: [Compound] = 9 which looks like it's comparing the
correct two integer values.

Have I not Dimensioned my recordset properly? I dimensioned it as a normal
Recordset and here's the command to open it.

Set rstMat = dbscurrent.OpenRecordset("Mat", dbOpenTable)

Thanks,
Kathy

The argument needs to be a string (so in quotes), and the string needs to
look just like the WHERE clause of a query.

Dim strWhere As String
strWhere = "[Comp] = " & TempComp
Debug.Print strWhere
rs.FindFirst strWhere

If it fails, open the Immediate window (Ctrl+G) and see what the Debug.Print
line dumped there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I don't know what I am doing here.

I have a recordset and I want to use the Recordset.Findfirst command to
find
a record, but I can't seem to get my syntax correct.

I have an integer variable I'll call TempComp.
The Comp field in my table is an integer.

I essentially would like to do the following:

Recordset.Findfirst Comp = TempComp
If Not Recordset.Nomatch Then
Recordset.Field2 = AnotherVariable
Endif

Can anyone please help with this? Your help would be greatly appreciated.

Thanks,
Kathy

 

[Allen Browne]

Ah: you opened a Table type recordset instead of a Dynaset type. Try:
Set rstMat = dbscurrent.OpenRecordset("Mat", dbOpenDynaset)

dbOpenDynaset is the default type for any recordset opened via a query, SQL
statement or on an attached table. Since it is so much more flexible, and
since you must use that type once the database is split (as you may want to
do some day), explicitly opening that type is probably the best approach.

If you must use a dbOpenTable type for some reason, you will need to specify
an index by name, and use the arcane Seek syntax instead of the Find
methods.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

Thanks for getting back to me so quickly.

I added/changed the code as you suggested and I still get an error.

I get the "Operation is not supported for this type of object."

The debug shows: [Compound] = 9 which looks like it's comparing the
correct two integer values.

Have I not Dimensioned my recordset properly? I dimensioned it as a normal
Recordset and here's the command to open it.

Set rstMat = dbscurrent.OpenRecordset("Mat", dbOpenTable)

Thanks,
Kathy

The argument needs to be a string (so in quotes), and the string needs to
look just like the WHERE clause of a query.

Dim strWhere As String
strWhere = "[Comp] = " & TempComp
Debug.Print strWhere
rs.FindFirst strWhere

If it fails, open the Immediate window (Ctrl+G) and see what the
Debug.Print
line dumped there.

I don't know what I am doing here.

I have a recordset and I want to use the Recordset.Findfirst command to
find
a record, but I can't seem to get my syntax correct.

I have an integer variable I'll call TempComp.
The Comp field in my table is an integer.

I essentially would like to do the following:

Recordset.Findfirst Comp = TempComp
If Not Recordset.Nomatch Then
Recordset.Field2 = AnotherVariable
Endif

Can anyone please help with this? Your help would be greatly
appreciated.

 

[KmhComputer]

Thank you, thank you! It works like a charm now!

Kathy

Ah: you opened a Table type recordset instead of a Dynaset type. Try:
Set rstMat = dbscurrent.OpenRecordset("Mat", dbOpenDynaset)

dbOpenDynaset is the default type for any recordset opened via a query, SQL
statement or on an attached table. Since it is so much more flexible, and
since you must use that type once the database is split (as you may want to
do some day), explicitly opening that type is probably the best approach.

If you must use a dbOpenTable type for some reason, you will need to specify
an index by name, and use the arcane Seek syntax instead of the Find
methods.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

Thanks for getting back to me so quickly.

I added/changed the code as you suggested and I still get an error.

I get the "Operation is not supported for this type of object."

The debug shows: [Compound] = 9 which looks like it's comparing the
correct two integer values.

Have I not Dimensioned my recordset properly? I dimensioned it as a normal
Recordset and here's the command to open it.

Set rstMat = dbscurrent.OpenRecordset("Mat", dbOpenTable)

Thanks,
Kathy

The argument needs to be a string (so in quotes), and the string needs to
look just like the WHERE clause of a query.

Dim strWhere As String
strWhere = "[Comp] = " & TempComp
Debug.Print strWhere
rs.FindFirst strWhere

If it fails, open the Immediate window (Ctrl+G) and see what the
Debug.Print
line dumped there.

I don't know what I am doing here.

I have a recordset and I want to use the Recordset.Findfirst command to
find
a record, but I can't seem to get my syntax correct.

I have an integer variable I'll call TempComp.
The Comp field in my table is an integer.

I essentially would like to do the following:

Recordset.Findfirst Comp = TempComp
If Not Recordset.Nomatch Then
Recordset.Field2 = AnotherVariable
Endif

Can anyone please help with this? Your help would be greatly
appreciated.