Assign db field data to text item

Databases
1

sql = “SELECT X.ID, X.Contact_ID, X.Card_Year, X.Card_Sent, X.Card_Received, X.Notes, C.ID, C.Last_Name, C.First_Name FROM XmasCardYear X, Contacts C”+_
" WHERE X.ID = ’ “+ me.Cell(me.ListIndex,0) +” ’ AND X.Contact_ID = C.ID"

fldCardID.Text = rs.Field(“ID”).StringValue //this does not work because we have 2 ID fields in the select stmt
fldCardID.Text = me.cell(me.ListIndex,0)

I am selecting a row of data from a list box which is loaded from SQLite Database to display in a detail area with field data from the selection. As can be seen from the sql select statement I am retrieving the ID field from 2 different tables. I need to load the X.ID into a invisible field on the detail form. If I assign the value of “ID” I don’t get the correct table ID. I have a work around using the listbox index, but I was curious as to how to use the rs.field to get the correct table.

I tried rs.field (x.ID) and rs.field (“x.ID”), but that does not compute.

Thanks for any help.

2

Change your select statement to:

sql = "SELECT X.ID AS MyID, X.Contact_ID, X.Card_Year, X.Card_Sent, X.Card_Received," +_ "X.Notes, C.ID, C.Last_Name, C.First_Name FROM XmasCardYear X, Contacts C"+_ " WHERE X.ID = ' "+ me.Cell(me.ListIndex,0) +" ' AND X.Contact_ID = C.ID"
And then use rs.field as:

fldCardID.Text = rs.Field("MyID").StringValue
You should find this will work.

3

Also, I think caps matter so “X.ID” should work.

4

[quote=49620:@Simon Berridge]Change your select statement to:

sql = "SELECT X.ID AS MyID, X.Contact_ID, X.Card_Year, X.Card_Sent, X.Card_Received," +_ "X.Notes, C.ID, C.Last_Name, C.First_Name FROM XmasCardYear X, Contacts C"+_ " WHERE X.ID = ' "+ me.Cell(me.ListIndex,0) +" ' AND X.Contact_ID = C.ID"
And then use rs.field as:

fldCardID.Text = rs.Field("MyID").StringValue
You should find this will work.[/quote]

Thanks, worked perfectly.

5

This does not work either, but thanks for the suggestion.