retrieving table information with an SQL statement

[Guest]

Hello,
I have a form with a text box. I want the value of the text box to display
the names of certain judges. I have a select statement as follows:
Set rs = CurrentDb.OpenRecordset( _
"SELECT JudgeID from tblChosenJustices WHERE CaseName = " &
Form_frmAllData.CaseName _
& "AND JudgeValue=" & varJudgeValue)
This part seems to work fine. Each judge is assigned a certain numeric
value and I now want to compare this value to the value (JusticeID) in
another table which is simply a 2 field list of judge names and judge values,
for instance Ruth Bader Ginsberg (JusticeName)=18(JusticeID). I want the
justice's name to show in the text box, so I did the following:

MySQL = ("SELECT JusticeName FROM tblJusticenames WHERE" &
rs.Fields("JudgeId") & "=" & TblJusticeNames.JusticeID)
Next intI
Do Until rs.EOF
rs.MoveNext
Loop
rs.Close

Set rs = Nothing
End If

Me.txtAuthorofMajority.Value = ???
Nothing seems to work. I'm extremely confused at this point.

Thank you for any help you can provide.

 

[Guest]

Hi Joanne,

replies in order;

Hello,
I have a form with a text box. I want the value of the text box to display
the names of certain judges. I have a select statement as follows:
Set rs = CurrentDb.OpenRecordset( _
"SELECT JudgeID from tblChosenJustices WHERE CaseName = " &
Form_frmAllData.CaseName _
& "AND JudgeValue=" & varJudgeValue)
This part seems to work fine. Each judge is assigned a certain numeric
value and I now want to compare this value to the value (JusticeID) in
another table which is simply a 2 field list of judge names and judge values,
for instance Ruth Bader Ginsberg (JusticeName)=18(JusticeID). I want the
justice's name to show in the text box, so I did the following:

MySQL = ("SELECT JusticeName FROM tblJusticenames WHERE" &
rs.Fields("JudgeId") & "=" & TblJusticeNames.JusticeID)
Next intI
Do Until rs.EOF
rs.MoveNext
Loop
rs.Close

Set rs = Nothing
End If

Is Me.txtAuthorofMajority=MySQL?
your Select statement is outside your loop, so fires just once, not sure if
you were meaning for it too populate a listbox showing more than 1 name? If
you just have a combobox bound to JudgeID, with it's rowsource pointing at a
query which links the Judge Table to the Judges name table, hide the first
row, and it will automatically show the judges name after your first
openrecordset code.

Me.txtAuthorofMajority.Value = ???
Nothing seems to work. I'm extremely confused at this point.

Thank you for any help you can provide.

Not sure I'm fully understanding what you are trying to acheive,

TonyT..

 

[Guest]

Hi,
Actually it's a text box. Could that be the problem? Yes, I do want the
textbox to be equal to MySQL but it just won't do it even with only one
go-round. I thought I would add the loop once I got it to work even one time!

Thanks very much.

 

[Guest]

I guess my biggest problem here is that the value of the text box becomes the
value of the SQL string, i.e the select statement itself rather than the
results of the select statement. Could you possibly tell me how to pull the
results into the value of the text box rather than the SQL string?

Thank you.

 

[Guest]

Hi again Joanne,

The MySQL route is only really valid for a listbox or combobox as they have
rowsource properties which you can set to the SQL statement, for a textbox
you'd be better off with;
intJudge = rs.Fields("JudgeId")
DLookup("JusticeName", "tblJusticenames", "JudgeId = " & intJudge & "")

where intJudge is the value from the first MySQL code you posted.

hope that works,

TonyT..

 

[Guest]

Thank you for your answer. I think it will help.