[RESOLVED] recordset SQL (similar field in seperate tables rst!ansID)

[Astro]

Hello there,

i'm working on a recordset.Open (sql statement)

st.Open "select * from qns_table, ans_table, ss_table where (some conditions)", cn, adOpenDynamic, adLockOptimistic

and i'm adding them into a list box

Me.List3.AddItem rst!ansID & ";" & etc...etc... (take note of ansID)

the problem lies at 'ansID' being part of ans_table, as well as ss_table and when i add rst!ansID, the application gets confused because it doesn't know if this ansID belongs to either ans_table or ss_table.

i have tried alternative methods
i.e, rst!ans_table.ansID - apparently the syntax is not correct.

can someone shed light here?

Thank you very much =)

Astro

[dannymking]

In your SQL specify the actual fields that you want, this way you can assign an alias for the field names.. for example

"SELECT ans_table.a_ansID as ansID, ss_table.ansID as s_ansID..."

You can also assign alias against the tables to help reduce the size of the sql string

e.g.

"SELECT a.ansID as a_ansID, s.ansID as s_ansID FROM ans_table a, ss_table s..."

[Astro]

Hahaha that's absolutely brilliant!

Ok erm one more? =)

"select * from ans_table, ss_table where ss_table.qnsID = " & List1.ItemData(List1.ListIndex)

Any ideas if this is likely to work? Tried several times, different ways.
I'm a lil skeptical bout sql working with vba with =" & List.blah blah blah.

Tough luck so far.

Thanks

Astro

[dannymking]

It will work if you are building the SQL before opening the recordset.. it won't work directly inside the SQL as you will need to refer to the form like..

Forms!FormName!List1.ItemData(List1.ListIndex

[Astro]

Bah! I'm quite helpless now Danny.

rst.Open "select ss_table.ansID as ss_table_ansID, ans_table.answer as ans_table_answer from ans_table, ss_table where ans_table.ansID = ss_table.ansID and ss_table.qnsID =" & "List1.ItemData(List1.ListIndex)", cn, adOpenDynamic, adLockOptimistic
rst.MoveFirst
Do While Not rst.EOF
Me.List3.AddItem rst!ss_table_ansID & ";" & rst!ans_table_answer & ";"

any easier way to this?

Thanks

[dannymking]

Seeing as you are opening the recordset with the sql then you will need to either code in the reference to the form like so..

rst.Open "select ss_table.ansID as ss_table_ansID, ans_table.answer as
ans_table_answer from ans_table, ss_table where ans_table.ansID =
ss_table.ansID and ss_table.qnsID =" & Forms!FormName!List1.ItemData
(List1.ListIndex), cn, adOpenDynamic, adLockOptimistic

The FormName needs to replicate your form's name..

Another way of doing this without using the form name is to create the sql as a string then open the recordset..

Dim strSQL as String
  strSQL = "select ss_table.ansID as ss_table_ansID, ans_table.answer as " & _
  "ans_table_answer from ans_table, ss_table where ans_table.ansID = " & _
  "ss_table.ansID and ss_table.qnsID =" & List1.ItemData(List1.ListIndex)
  rst.Open strSQL, cn, adOpenDynamic, adLockOptimistic

It will also depend on the contents of the listbox and whether or not qnsID is numeric or text..

[Astro]

Private Sub List0_Click()

Rem fetch
Me.List1.RowSource = ""
rst.Open "select [qns_table]qnsID from qns_table where [qns_table]qnsID =" & [Forms]![Form3]![List0.ItemData(List0.ListIndex)], cn, adOpenDynamic, adLockOptimistic
rst.MoveFirst
'Do While Not rst.EOF

'Me.List1.AddItem rst!qns_table_qnsID & ";"
'rst.MoveNext
'Loop
rst.Close
End Sub

I've tried both methods but still prefer the one which requires specifying the form.
i'm getting closer with this. now it says that Access cannot read the List0.ItemData(List0.ListIndex).

Thanks

Astro

[dannymking]

not..

]![List0.ItemData(List0.ListIndex)],

but

].List0.ItemData(List0.ListIndex),

[Astro]

Hey Danny,

I got it!

Thanks

Astro