SQL SELECT code behind a form

  • Thread starter Thread starter Leslie J Crouchman
  • Start date Start date
L

Leslie J Crouchman

Forgive me but I am new to Microsoft Access VBA coding. I have taken over
the administration of a charity's database and I would like to add a form
using a partial search with a text and a list box both of them unbound. At
present the code below works OK but now I want to add a further field called
Serial_No from the database table1, the field is a long integer and the
primary key and I intend to hide it on the form.

Private Sub txtInName_Change()
Dim AccessStr As String

AccessStr = "SELECT DISTINCTROW Surname, Forenames From table1 " _
& "Where Surname Like '" & Me.txtInName.Text & "*'" _
& "ORDER BY Surname, Forenames;"
With Me.lstMembers
Me.Requery
.RowSource = AccessStr
If .ListCount < 0 Then
Me.cmdOK.Enabled = False
Else
Me.cmdOK.Enabled = True
End If
End With
End Sub

If I add the field Serial_No it appears as an input box. What do I have to
do to add the field Serial_No?
Your help would be appreciated.
 
Leslie said:
Forgive me but I am new to Microsoft Access VBA coding. I have taken over
the administration of a charity's database and I would like to add a form
using a partial search with a text and a list box both of them unbound. At
present the code below works OK but now I want to add a further field called
Serial_No from the database table1, the field is a long integer and the
primary key and I intend to hide it on the form.

Private Sub txtInName_Change()
Dim AccessStr As String

AccessStr = "SELECT DISTINCTROW Surname, Forenames From table1 " _
& "Where Surname Like '" & Me.txtInName.Text & "*'" _
& "ORDER BY Surname, Forenames;"
With Me.lstMembers
Me.Requery
.RowSource = AccessStr
If .ListCount < 0 Then
Me.cmdOK.Enabled = False
Else
Me.cmdOK.Enabled = True
End If
End With
End Sub

If I add the field Serial_No it appears as an input box. What do I have to
do to add the field Serial_No?
Click to expand...


I'm sorry, I think I anawered the wrong question in your
other post.

If you're being prompted for a value for Serial_No, it means
that the name of the field in the table is spelled
differently than what you added to the query.

If the field in the table has a blank instead of an _,
enclose the name in square brackets in the query:
... , Forenames, [Serial No] From ...
 
Back
Top