Seek Method Problem 1

[Gerbers404]

Hi there, I am getting a Run-time Error '3219' - Invalid Operation Error in the following code.

Private Sub JN_AfterUpdate()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblSpecCondCurrent", dbOpenTable)
rs.Index = "Cond_ID"
rs.Seek "=", Me.Cond_ID
Me.Bookmark = rs.Bookmark
Me.Volume.Visible = True
End Sub

I get the error as I try to set the recordset. Any Ideas?

Thanks!

Gerbers404

 

[Frink]

Hallo,

Have you tried any of the other type constants, like dbOpenDynaset?

- Frink

 

[Gerbers404]

No, I haven't tried that... Will try some different ones after the weekend is over. Almost wondering if it is a reference issue.

Thanks.



Gerbers404

 

[rstitzel]

Make sure you have gone into your module and clicked on tools and then references. Add MICROSOFT DA0 3.6 OBJECT LIBRARY.

Modified Code:

Private Sub JN_AfterUpdate()
Dim db As dao.Database
Dim rs As dao.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblSpecCondCurrent&quot
rs.Index = "Cond_ID"
rs.Seek "=", Me.Cond_ID
Me.Bookmark = rs.Bookmark
Me.Volume.Visible = True
End Sub

Hope this helps. Thanks

 

[Gerbers404]

rstitzel,

I do have DAO 3.6 in the refereneces, it's about the 6th reference on the list. I modified my code to what you suggested, now I am getting a different error. The error now comes as I set the index, rs.Index = "Cond_ID". The error message tells me that the "Operation is not supported for this type of object."
Could this problem be arising because I'm trying to call these methods on a form that is already bound to the table, "tblSpecCondCurrent" that I'm trying to seek?

Thanks for your help!

Gerbers404

 

[danvlas]

If your tables are linked, you can't use dbOpenTable.
Use dbOpenDynaset. And Seek is also not available for linked tables. Use FindFirst instead...

Good luck




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Gerbers404]

Daniel,

OK, this is a linked table. I have to set a connection to the backend DB that this table resides in before I can use the seek method? I was origianlly using the FindFirst method, but hadn't been able to get it to work like I'd wanted.

Here's what I'm trying to do. I have a little form, bound to a linked table[tblSpecCondCurrent], that has a combobox on it [JN]. When I select a JobNumber from the cbobox, I want to retrieve the volume associated with the JobNumber I selected. The problem seems to be that I can have more than one instance of a JobNumber in the table, all with different volumes. The Prmary Key of the table is [Cond_ID] which is a date/time field. By using the FindFirst method, all I could really do was find the first record in the table with the specific job number that I selected in the cbobox, not necissarily the one with the correct volume. After your response, I tried to use the FindFirst to locate on [Cond_ID], but it didn't work. here's the attempt...

    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Cond_ID] = #" & Me.Cond_ID & "#"
    Me.Bookmark = rs.Bookmark
    Me.Volume.Visible = True

When I stepped through the code, Me.Cond_ID has the value of the first Cond_ID in the table, and not the Cond_ID of the record I selected in the cbobox. Should I perhaps set a bookmark before the FindFirst?

Thanks!

Gerbers404

 

[danvlas]

As I see it, you're searching on Cond_ID box on the form, which is probably bound to the field in the table, not on the JN combo box - unbound.

Change the JN combo box to return the Cond_ID value (bound column) and to display the JobNumber (or better both)

With me.RecordsetClone
.FindFirst "[Cond_ID] = #" & Me.JN & "#"
If .NoMatch Then
MsgBox "No record found"
Me.Volume.Visible = False
Else
Me.Bookmark = .Bookmark
Me.Volume.Visible = True
End If
End With

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Gerbers404]

Daniel,

Thank you very much! I got so turned around on this problem that I just created more problems! Your answer fixed me up. I actually smacked my head and said "duh" when I read your response. Thanks again.

Gerbers404