Question Please tell me what wrong

[johnnychow]

The following work on the combo box

Private Sub Given_Name_AfterUpdate()

Dim conn As ADODB.Connection
Dim rstset As New ADODB.Recordset
Dim sql As String
Set conn = CurrentProject.Connection
sql = "select [Members ID and Name Query].[Member ID] from [Members ID and Name Query] where" _
& "[Members ID and Name Query].[Family Name] like [Forms]![Members]![Family Name] and [Members ID and Name Query].[Given Name] Like [Forms]![Members]![Given Name]"
Me.ID.RowSource = sql
Me.ID.Requery

End Sub


But amend it and does not work on the text box, why? Please, help!!!!


Private Sub Given_Name_AfterUpdate()

Dim conn As ADODB.Connection
Dim rstset As New ADODB.Recordset
Dim sql As String
Set conn = CurrentProject.Connection
sql = "select [Members ID and Name Query].[Member ID] from [Members ID and Name Query] where" _
& "[Members ID and Name Query].[Family Name] like [Forms]![Members]![Family Name] and [Members ID and Name Query].[Given Name] Like [Forms]![Members]![Given Name]"
rstset.Open sql, conn <----------------------------debug
Me.Text11.Text = rstset.GetString

End Sub

 

[Galaxiom]

The sql command does not see the Access forms. You must concatenate the values from the form into the sql string.

BTW. The way you have used the Like operator is as though you had used the equal operator so you would be better using it.

[Members ID and Name Query] is a rather clumsy name for a query. Even without changing the name you could make the sql a lot more concise by leaving out the query name everywhere except in the FROM. Since this is the only source the fields will automatically come from this query.

Also please post your code in a code box.
http://www.access-programmers.co.uk/forums/showthread.php?goto=newpost&t=200247

Most developers would put the WHERE at the beginning of a continued line rather than at the end.

Last but not least, welcome to the forum.