[Access] How to reference a cell in Access?

[spidermman]

This is the new code that I created:



Private Sub Command132_Click()

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

Dim myRecordSet As New ADODB.Connection
myRecordSet.ActiveConnection = cnn


Dim mySQL As String

mySQL = "SELECT [lifting program].Shipment_Number, [lifting program].product1, [lifting program].product2 "
mySQL = mySQL & " FROM [lifting program]"
mySQL = mySQL & " WHERE ((([lifting program].Shipment_Number) = 690392))"

myRecordSet.Open mySQL

List60.Value = myRecordSet.Fields("product1").Value
List72.Value = myRecordSet.Fields("product2").Value

End Sub



I used a query to obtain 3 fields from the table ?lifting program?.
I created a select query to select a particular shipment number from the shipment_number field. Then I would store the results of the query in a recordset. So I set two list box whereby one list box will contain the product 1 value and the second list box will contain the product 2 value.

However when I run this event procedure, there is no results.

Does anyone knows y?

thanks.

[Carl A]

Change myRecordset to a Recordset instead of a Connection


Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection


Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnn


Dim mySQL As String

mySQL = "SELECT [lifting program].Shipment_Number, [lifting program].product1, [lifting program].product2 "
mySQL = mySQL & " FROM [lifting program]"
mySQL = mySQL & " WHERE ((([lifting program].Shipment_Number) = 690392))"

myRecordSet.Open mySQL

List60.Value = myRecordSet.Fields("product1").Value
List72.Value = myRecordSet.Fields("product2").Value

End Sub

[spidermman]

i tried to change to
Dim myRecordSet As New ADODB.Recordset
but it also couldnt work.

i have created some new code but it couldnt work as there is invalid object error.

this is my new code:


Private Sub Command11_Click()

Dim cnobject As ADODB.Connection
Dim rsobject As ADODB.Recordset

Set cnobject = New ADODB.Connection
Set rsobject = New ADODB.Recordset

cnobject.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=H:\Project3\schedulingtool - V1.mdb;"
rsobject.Open "SELECT * FROM [lifting program]", cnobject, adOpenStatic

rsobject.Find "Shipment_Number = 690392"

Do While Not rsobject.EOF
Debug.Print rsobject.Fields("Product1")
rsobject.Find "Shipmet_Number = 690392", 1
Loop

rsobject.Close
cnobject.Close

Set rsobject = Nothing
Set cnobject = Nothing

End Sub



does anyone knows y it couldnt work? thanks.

[CreganTur]

Welcome, webslinger, to VBAX- always good to see new members.

Assuming that your SQL statement works (did you test it independently as a Query to check your logic?) then try this code:

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnn


Dim mySQL As String

mySQL = "SELECT [lifting program].Shipment_Number, [lifting program].product1, [lifting program].product2 "
mySQL = mySQL & " FROM [lifting program]"
mySQL = mySQL & " WHERE ((([lifting program].Shipment_Number) = 690392))"

myRecordSet.Open mySQL

List60.Value = myRecordSet.Fields(1).Value
List72.Value = myRecordSet.Fields(2).Value

End Sub


I believe the issue has to do with how you were using the .Fields() function. In ADO recordsets, fields refers to the the number representing the field order in the created recordset. In your case, Product1 is in position 1, and Product2 is in position 2- this is because Access starts numbering at 0 (unless you use Option Base 1).

Try this and see if it doesn't give you the results you're looking for.

HTH:thumb