SQL Select in Form Code

[Leslie J Crouchman]

Forgive me but I am new to this Access VBA coding. I have taken over the
administration of a charity's database and I want to add a form with a
partial name search using a text and a list box, both of them unbound. At
present the code works but now I want to add another field from the database
table1 called Serial_no, it's a long integer and the index key and I intend
to hide it on the list box. At present the code is:

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

When I add the field it apears as an input box. What do I have to do to add
the field Serial_No to the above?
Your help would be appreciated.

 

[Richard]

Forgive me but I am new to this Access VBA coding. I have taken over the
administration of a charity's database and I want to add a form with a
partial name search using a text and a list box, both of them unbound. At
present the code works but now I want to add another field from the database
table1 called Serial_no, it's a long integer and the index key and I intend
to hide it on the list box. At present the code is:

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

When I add the field it apears as an input box. What do I have to do to add
the field Serial_No to the above?
Your help would be appreciated.

What I would just do here is to add this code to the rowsource of the
listbox. In the criteria put the criteria as being the textbox on your
form so "LIKE forms![frmwhatever]![txtInName] * "*".

If you really want to use VBA to do this then just swap your
Me.txtInName.Text for Forms![frmwhatever]![txtInName]. VBA
understands the Me. notation because the VBA module is a property of
the the form, but the RowSource is actually SQL so you need to tell it
where to find the value - through the forms collection.

Richard


Its a good job Einstein never had to fill out one of these Government Grant applications or we never would have found out what e equalled. The West Wing

 

[Richard]

Forgive me but I am new to this Access VBA coding. I have taken over the
administration of a charity's database and I want to add a form with a
partial name search using a text and a list box, both of them unbound. At
present the code works but now I want to add another field from the database
table1 called Serial_no, it's a long integer and the index key and I intend
to hide it on the list box. At present the code is:

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

When I add the field it apears as an input box. What do I have to do to add
the field Serial_No to the above?
Your help would be appreciated.

Sorry, to your second question. Its not clear what you want to add
the field Serial_no to? If you want to add it to your form you have
to ensure that the field is part of the form's recordsource. Then you
can put it on the form wherever you want.

Richard
Its a good job Einstein never had to fill out one of these Government Grant applications or we never would have found out what e equalled. The West Wing

 

[Marshall Barton]

Forgive me but I am new to this Access VBA coding. I have taken over the
administration of a charity's database and I want to add a form with a
partial name search using a text and a list box, both of them unbound. At
present the code works but now I want to add another field from the database
table1 called Serial_no, it's a long integer and the index key and I intend
to hide it on the list box. At present the code is:

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

When I add the field it apears as an input box. What do I have to do to add
the field Serial_No to the above?

Just add the field's name to the query's field list:
...."SELECT DISTINCTROW Surname, Forenames, Serial_No From
....
and make sure you change the ColumnCount property to 3. If
the serial no field is the value you want the list box to
remember, set the BoundColumn to 3.