Seek Method (DAO) (Access 2002)

Topic

New Reply

I am trying to use the Seek method to determine if a specific record exists. I have created a recordset with two indexes. One of the indexes is a text field and the other is numeric field. Seeking just on the text field works fine, but when I added the index to the numeric field, I get an error. The error is on the Seek line. The numeric field is related to an Autonum field on a related table and therefore cannot be changed (clear as mud??).

The seek code I’m using (see attachment) uses the value from a combobox as the value to be compared. That value is a string, by default. I tried to wrap the value in a type converter [ ” CInt(cboReport.Value) ” ], but that did nothing. I have tried to format that control as numeric, which didn’t appear to have an effect. After changing the format of the control, the value as displayed in the Watch Window showed it to be numeric instead of a string (that is, there were now no quote marks around the value), but I still get the error.

Does anyone have any ideas. They will be greatly appreciated.

Reply | Quote

Replies

Your code sets the recordset to use one index, then replaces it by another index; it doesn’t combine the two indexes.

Workaround 1: in the design of the table, define a composite index on the combination of Item and ReportID. Use the name of this index.

Workaround2: use FindFirst instead of seek:

rstGroupItem.FindFirst(“Item = ” & tbxGrpAdd.Value & ” And ReportID = ” & cboReport.Value)

(this assumes numeric values, add quotes around text values; you may have to use dbOpenDynaset instead of dbOpenTable)

Reply | Quote

Your code sets the recordset to use one index, then replaces it by another index; it doesn’t combine the two indexes.

Workaround 1: in the design of the table, define a composite index on the combination of Item and ReportID. Use the name of this index.

Workaround2: use FindFirst instead of seek:

rstGroupItem.FindFirst(“Item = ” & tbxGrpAdd.Value & ” And ReportID = ” & cboReport.Value)

(this assumes numeric values, add quotes around text values; you may have to use dbOpenDynaset instead of dbOpenTable)

Reply | Quote

If the value in cboReport is related to an autonumber field, it is a long integer, not an integer, regardless of the length of the value, and you have to cast it as a long in order to look for matches. Try CLng(cboReport.Value) and see if that changes the result.

Reply | Quote

If the value in cboReport is related to an autonumber field, it is a long integer, not an integer, regardless of the length of the value, and you have to cast it as a long in order to look for matches. Try CLng(cboReport.Value) and see if that changes the result.

Reply | Quote