SQL SELECT code behind a form

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.
 
M

Marshall Barton

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?


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 ...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top