getting a saved query displayed in txtfield from code

[bluesage]

hi, here are two different versions i tried, but neither work. i don't know what to do. what im trying to do is:

1.) i have a form and with a combobox, when the user selects a name

2.) i want the value to be sent to the query

3.) and the result displayed in a txtfield on the same form

============================================

Private Sub nomcbo_AfterUpdate()

Dim strSQL As String

strSQL = "SELECT COUNT(no_repas) AS repas FROM manger WHERE no_res = " & nomcbo.Value

nomcbo.SetFocus

If Me.nomcbo.Column(0, 1) = 1 Then
Me.repastxt.ControlSource = strSQL
End If

If Me.nomcbo.Column(0, 2) = 2 Then
Me.repastxt.ControlSource = strSQL
End If


===================================================


Private Sub nomcbo_AfterUpdate()
On Error Resume Next

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String


nomcbo.SetFocus
If nomcbo.Value > 0 Then
strSQL = "SELECT COUNT(no_repas) AS repas FROM manger WHERE [no_res] = " & nomcbo.Value

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF Then
Me.repastxt = rs("repas")
End If
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End If

=================================================

i was also thinking about making a query with lets say the name "repasqry", with the WHERE part saying:

WHERE manger.no_resident = [forms]![repasforms]![nomcbo.value]

don't know if the syntax is right, but something like this.

in this case: i would want the same thing, that when the user chooses a name, the value is sent to the query and the result is displayed on the same form in a text field name "repastxt". if thats possible? it probably easier with the first idea?

thanks

 

[PHV]

You may also consider the DCount function

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884