look up

  • Thread starter Thread starter J A
  • Start date Start date
J

J A

Hello

Can somebody help me with the following:

Am using this code to fill in a Invoice form the quantities and price based
on a given serial number. Everything worked fine till now untill I splited
the database in 2; 1 for the data & the other the rest. Now after filling
the serial number, the program stops on the code marked below with 4 >.

Private Sub SerialNumber_AfterUpdate()
Dim ThisDB As Database
Dim ProdList As Recordset
Dim LookFor As String

If IsNull(Me!SerialNumber) Then
Exit Sub
DoCmd.GoToControl "Forms![Invoices]![Percent] "
End If

'If not blank, look it up.
'First define DAO object variables
Set ThisDB = CurrentDb()
Set ProdList = ThisDB.OpenRecordset("Products")
ProdList.Index = "PrimaryKey" LookFor = Me![SerialNumber]
ProdList.Seek "=", LookFor
Click to expand...
Click to expand...
Click to expand...

If ProdList.NoMatch Then
Beep
Else
Me![Quantity] = [ProdList]![UnitsInStock]
Me![PricePerUnit] = [ProdList]![UnitPrice]
End If
[ProdList].Close
End Sub


Thanks in advance
 
Hi,
It's because Index and Seek only work on Table Type recordsets.
You can't open a Table Type recordset on a linked table.

You can use FindFirst instead of Seek

--
HTH
Dan Artuso, Access MVP


J A said:
Hello

Can somebody help me with the following:

Am using this code to fill in a Invoice form the quantities and price based
on a given serial number. Everything worked fine till now untill I splited
the database in 2; 1 for the data & the other the rest. Now after filling
the serial number, the program stops on the code marked below with 4 >.

Private Sub SerialNumber_AfterUpdate()
Dim ThisDB As Database
Dim ProdList As Recordset
Dim LookFor As String

If IsNull(Me!SerialNumber) Then
Exit Sub
DoCmd.GoToControl "Forms![Invoices]![Percent] "
End If

'If not blank, look it up.
'First define DAO object variables
Set ThisDB = CurrentDb()
Set ProdList = ThisDB.OpenRecordset("Products")
ProdList.Index = "PrimaryKey" LookFor = Me![SerialNumber]
ProdList.Seek "=", LookFor
Click to expand...
Click to expand...

If ProdList.NoMatch Then
Beep
Else
Me![Quantity] = [ProdList]![UnitsInStock]
Me![PricePerUnit] = [ProdList]![UnitPrice]
End If
[ProdList].Close
End Sub


Thanks in advance
Click to expand...
 
J A said:
Hello

Can somebody help me with the following:

Am using this code to fill in a Invoice form the quantities and price
based on a given serial number. Everything worked fine till now
untill I splited the database in 2; 1 for the data & the other the
rest. Now after filling the serial number, the program stops on the
code marked below with 4 >.

Private Sub SerialNumber_AfterUpdate()
Dim ThisDB As Database
Dim ProdList As Recordset
Dim LookFor As String

If IsNull(Me!SerialNumber) Then
Exit Sub
DoCmd.GoToControl "Forms![Invoices]![Percent] "
End If

'If not blank, look it up.
'First define DAO object variables
Set ThisDB = CurrentDb()
Set ProdList = ThisDB.OpenRecordset("Products")
ProdList.Index = "PrimaryKey" LookFor = Me![SerialNumber]
ProdList.Seek "=", LookFor
Click to expand...
Click to expand...

If ProdList.NoMatch Then
Beep
Else
Me![Quantity] = [ProdList]![UnitsInStock]
Me![PricePerUnit] = [ProdList]![UnitPrice]
End If
[ProdList].Close
End Sub


Thanks in advance
Click to expand...

You can only use the Seek method on table-type recordsets, and you can't
get a table-type recordset on a linked table -- OpenRecordset will give
you a dynaset instead. One workaround is to go ahead with the dynaset
but use .FindFirst (with suitable criteria) instead of .Seek. However,
this will be faster:

'---- start of revised code ----
Private Sub SerialNumber_AfterUpdate()

Dim ThisDB As Database
Dim ProdList As Recordset

If IsNull(Me!SerialNumber) Then
Exit Sub
DoCmd.GoToControl "Forms![Invoices]![Percent] "
End If

'If not blank, look it up.
Set ThisDB = CurrentDb()

Set ProdList = ThisDB.OpenRecordset( _
"SELECT UnitsInStock, UnitPrice FROM Products " & _
"WHERE SerialNumber='" & Me![SerialNumber] & "'")

'** NOTE: The above assumes SerialNumber is a text field.

With ProdList
If .EOF Then
Beep
Else
Me![Quantity] = ![UnitsInStock]
Me![PricePerUnit] = ![UnitPrice]
End If
.Close
End With

Set ProdList = Nothing
Set ThisDB = Nothing

End Sub
'---- end of revised code ----

Another workaround would be to open the linked database directly, as a
Database object, and open a table-type recordset from there. Then you
could use .Seek. For a single quick lookup, though, I don't think it's
worth it.
 
Am thankfull about your revised code, but the programm is still craching at
Set ProdList = This DB.OpenRecordset etc... thought I changed the
SerialNumber field to text.

Thank you again


Dirk Goldgar said:
J A said:
Hello

Can somebody help me with the following:

Am using this code to fill in a Invoice form the quantities and price
based on a given serial number. Everything worked fine till now
untill I splited the database in 2; 1 for the data & the other the
rest. Now after filling the serial number, the program stops on the
code marked below with 4 >.

Private Sub SerialNumber_AfterUpdate()
Dim ThisDB As Database
Dim ProdList As Recordset
Dim LookFor As String

If IsNull(Me!SerialNumber) Then
Exit Sub
DoCmd.GoToControl "Forms![Invoices]![Percent] "
End If

'If not blank, look it up.
'First define DAO object variables
Set ThisDB = CurrentDb()
Set ProdList = ThisDB.OpenRecordset("Products")
ProdList.Index = "PrimaryKey" LookFor = Me![SerialNumber]
ProdList.Seek "=", LookFor
Click to expand...

If ProdList.NoMatch Then
Beep
Else
Me![Quantity] = [ProdList]![UnitsInStock]
Me![PricePerUnit] = [ProdList]![UnitPrice]
End If
[ProdList].Close
End Sub


Thanks in advance
Click to expand...

You can only use the Seek method on table-type recordsets, and you can't
get a table-type recordset on a linked table -- OpenRecordset will give
you a dynaset instead. One workaround is to go ahead with the dynaset
but use .FindFirst (with suitable criteria) instead of .Seek. However,
this will be faster:

'---- start of revised code ----
Private Sub SerialNumber_AfterUpdate()

Dim ThisDB As Database
Dim ProdList As Recordset

If IsNull(Me!SerialNumber) Then
Exit Sub
DoCmd.GoToControl "Forms![Invoices]![Percent] "
End If

'If not blank, look it up.
Set ThisDB = CurrentDb()

Set ProdList = ThisDB.OpenRecordset( _
"SELECT UnitsInStock, UnitPrice FROM Products " & _
"WHERE SerialNumber='" & Me![SerialNumber] & "'")

'** NOTE: The above assumes SerialNumber is a text field.

With ProdList
If .EOF Then
Beep
Else
Me![Quantity] = ![UnitsInStock]
Me![PricePerUnit] = ![UnitPrice]
End If
.Close
End With

Set ProdList = Nothing
Set ThisDB = Nothing

End Sub
'---- end of revised code ----

Another workaround would be to open the linked database directly, as a
Database object, and open a table-type recordset from there. Then you
could use .Seek. For a single quick lookup, though, I don't think it's
worth it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
J A said:
Am thankfull about your revised code, but the programm is still
craching at Set ProdList = This DB.OpenRecordset etc... thought I
changed the SerialNumber field to text.
Click to expand...

I didn't intend for you to change the SerialNumber field to text if it
wasn't text already; I just meant that the code would have to be
modified if the field weren't text. However, if it's failing on the Set
ProdList statement, I suspect that you have references to both the DAO
library and the ADODB library (which both define Recordset objects), and
you need to tell Access which type of recordset this is. Change
Dim ProdList As Recordset
Click to expand...

to

Dim ProdList As DAO.Recordset

and see if it works.

Oh, and please don't call it "crashing" if the program just puts up an
error message and doesn't do what you want, but continues to run
otherwise. That's not crashing, that's just failing.
 
I thank u very much, it did work.


Dirk Goldgar said:
I didn't intend for you to change the SerialNumber field to text if it
wasn't text already; I just meant that the code would have to be
modified if the field weren't text. However, if it's failing on the Set
ProdList statement, I suspect that you have references to both the DAO
library and the ADODB library (which both define Recordset objects), and
you need to tell Access which type of recordset this is. Change


to

Dim ProdList As DAO.Recordset

and see if it works.

Oh, and please don't call it "crashing" if the program just puts up an
error message and doesn't do what you want, but continues to run
otherwise. That's not crashing, that's just failing.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top