Access 2000 & Linked SQL tables problem

[Bruce Lawrence]

I'm trying to gather a single record from a SQL table. The SQL tables
are linked into the Acc DB.

Here is my code I'm trying to do. I should note that this DB was
converted from Acc97
I've tried to add things like "dao" to the variable declarations.
Dim CurrentDataBase As DAO.Database
Set CurrentDataBase = CurrentDb

Dim qryPowder As DAO.Recordset
Set qryPowder = CurrentDataBase.OpenRecordset("SELECT DISTINCT Powder.*
FROM Powder where Grade = '" & txtGrade & "' And Batch = '" & txtBatch
& "'", dbOpenDynaset)
If qryPowder.EOF Then
MsgBox "Error reading Powder Table"
txtGrade.SetFocus
DoCmd.Hourglass False
Exit Sub
Else
qryPowder.MoveFirst
SinteredDensity = qryPowder.SinteredDensity
WeightLossFactor = qryPowder.WeightLossFactor
End If
The error occurs when the code gets to "sintereddensity" just after
movefirst. The message is "Compile Error: Method or DataMember not
found"

Thanks for any help

[deluxeinformation]

Bruce Lawrence wrote:

I'm trying to gather a single record from a SQL table. The SQL tables
are linked into the Acc DB.

Here is my code I'm trying to do. I should note that this DB was
converted from Acc97
I've tried to add things like "dao" to the variable declarations.
Dim CurrentDataBase As DAO.Database
Set CurrentDataBase = CurrentDb

Dim qryPowder As DAO.Recordset
Set qryPowder = CurrentDataBase.OpenRecordset("SELECT DISTINCT Powder.*
FROM Powder where Grade = '" & txtGrade & "' And Batch = '" & txtBatch
& "'", dbOpenDynaset)
If qryPowder.EOF Then
MsgBox "Error reading Powder Table"
txtGrade.SetFocus
DoCmd.Hourglass False
Exit Sub
Else
qryPowder.MoveFirst
SinteredDensity = qryPowder.SinteredDensity
WeightLossFactor = qryPowder.WeightLossFactor
End If
The error occurs when the code gets to "sintereddensity" just after
movefirst. The message is "Compile Error: Method or DataMember not
found"

Thanks for any help

The code you wrote should work if you replace qryPowder.SinteredDensity
with qryPowder!SinteredDensity or qryPowder.Fields("SinteredDensity")
and qryPowder.WeightLossFactor with qryPowder!WeightLossFactor or
qryPowder.Fields("WeightLossFactor") provided that "SinteredDensity"
and "WeightLossFactor" are fields in your Powder table. And while your
code should work with these changes, it is generally customary to use
'rs' or 'rst' as a prefix for recordsets and 'qry' as a prefix for
queries, e.g. 'rstPowder' as opposed to 'qryPowder'. Hope this helps!

Bruce

[Bruce Lawrence]

de***************@gmail.com wrote:

Bruce Lawrence wrote:

I'm trying to gather a single record from a SQL table. The SQL tables
are linked into the Acc DB.

Here is my code I'm trying to do. I should note that this DB was
converted from Acc97
I've tried to add things like "dao" to the variable declarations.
Dim CurrentDataBase As DAO.Database
Set CurrentDataBase = CurrentDb

Dim qryPowder As DAO.Recordset
Set qryPowder = CurrentDataBase.OpenRecordset("SELECT DISTINCT Powder.*
FROM Powder where Grade = '" & txtGrade & "' And Batch = '" & txtBatch
& "'", dbOpenDynaset)
If qryPowder.EOF Then
MsgBox "Error reading Powder Table"
txtGrade.SetFocus
DoCmd.Hourglass False
Exit Sub
Else
qryPowder.MoveFirst
SinteredDensity = qryPowder.SinteredDensity
WeightLossFactor = qryPowder.WeightLossFactor
End If
The error occurs when the code gets to "sintereddensity" just after
movefirst. The message is "Compile Error: Method or DataMember not
found"

Thanks for any help

The code you wrote should work if you replace qryPowder.SinteredDensity
with qryPowder!SinteredDensity or qryPowder.Fields("SinteredDensity")
and qryPowder.WeightLossFactor with qryPowder!WeightLossFactor or
qryPowder.Fields("WeightLossFactor") provided that "SinteredDensity"
and "WeightLossFactor" are fields in your Powder table. And while your
code should work with these changes, it is generally customary to use
'rs' or 'rst' as a prefix for recordsets and 'qry' as a prefix for
queries, e.g. 'rstPowder' as opposed to 'qryPowder'. Hope this helps!

Bruce

Beautiful! I LOVE YOU