order by customer: sorting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I started a database on the access 2002 on the "order entry" template. On the
orm where u place orders i would like to install a list box that would ahve
customer names and when i click one it would put all there info in the form.
i almost got it working, i have to right click > remove filter/sorting befor
clicking a name in the list box will pull up that prsons info into the form,
how can i make it just automatically pulls up ppl i click on woth ut having
to select that option?
 
I take it that you're using the AfterUpdate event of the list to initiate
the Filtering. Just remove the filter before "re-filtering" for your next
customer.
Me.Filteron = False
'Your Filter = code here....
Me.Filteron = True

However, you might want to consider using a "Find" function rather than
Filter. It's usually faster than filtering/unfiltering each time. Just a
suggestion...
 
In addition to Al's suggestions, if you are only wanting to find one
customer, I would suggest you consider using a Combo Box control rather than
a List Box control. The Combo control is actually much better at this sort
of thing. It is also more user friendly because it allows them to find a
customer by typing in the name. If you set the combo box property AutoExpand
to Yes, it will follow the user's typing and position on the closest match.
Then when the user has selected a customer, add the following code to the
After Update event of the combo box. This is the most common way to do this.
Note - you will have to change the names in this sample to match the names
in your applcation:

Private Sub cboActivity_AfterUpdate()
Dim rst As Recordset
On Error GoTo cboActivity_AfterUpdate_Error

If Not IsNull(Me.cboActivity) Then
Set rst = Me.RecordsetClone
'Search the table for the value in the combo
rst.FindFirst "[Activity] = '" & Me.cboActivity & "'"
If rst.NoMatch Then
'Did Not find it in the table
If MsgBox("Add Activity Number " & Me.cboActivity _
& " To the Attribute Table", _
vbYesNo + vbQuestion + vbDefaultButton2, "Activity Not
Found") _
= vbYes Then
'Add a new record to the table
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Else
'Do Not Add a new record, Empty the combo and start over
Me.cboActivity = Null
End If
Else
'Makes the selected record the current record in the form
Me.Bookmark = rst.Bookmark
End If
End If
cboActivity_AfterUpdate_Exit:

On Error Resume Next
rst.Close
Set rst = Nothing
Exit Sub

cboActivity_AfterUpdate_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure cboActivity_AfterUpdate of VBA Document
Form_frmAttributetable"
GoTo cboActivity_AfterUpdate_Exit

End Sub
 
Thank you both for such timly responce! Klatuu I'm very interested in what u
had to say! I'm new to access and don't completely understand where to edit
your code u gave me could u mark where and with what for me if its not to
much to ask? thanx!

Klatuu said:
In addition to Al's suggestions, if you are only wanting to find one
customer, I would suggest you consider using a Combo Box control rather than
a List Box control. The Combo control is actually much better at this sort
of thing. It is also more user friendly because it allows them to find a
customer by typing in the name. If you set the combo box property AutoExpand
to Yes, it will follow the user's typing and position on the closest match.
Then when the user has selected a customer, add the following code to the
After Update event of the combo box. This is the most common way to do this.
Note - you will have to change the names in this sample to match the names
in your applcation:

Private Sub cboActivity_AfterUpdate()
Dim rst As Recordset
On Error GoTo cboActivity_AfterUpdate_Error

If Not IsNull(Me.cboActivity) Then
Set rst = Me.RecordsetClone
'Search the table for the value in the combo
rst.FindFirst "[Activity] = '" & Me.cboActivity & "'"
If rst.NoMatch Then
'Did Not find it in the table
If MsgBox("Add Activity Number " & Me.cboActivity _
& " To the Attribute Table", _
vbYesNo + vbQuestion + vbDefaultButton2, "Activity Not
Found") _
= vbYes Then
'Add a new record to the table
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Else
'Do Not Add a new record, Empty the combo and start over
Me.cboActivity = Null
End If
Else
'Makes the selected record the current record in the form
Me.Bookmark = rst.Bookmark
End If
End If
cboActivity_AfterUpdate_Exit:

On Error Resume Next
rst.Close
Set rst = Nothing
Exit Sub

cboActivity_AfterUpdate_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure cboActivity_AfterUpdate of VBA Document
Form_frmAttributetable"
GoTo cboActivity_AfterUpdate_Exit

End Sub



ppskidpps said:
I started a database on the access 2002 on the "order entry" template. On the
orm where u place orders i would like to install a list box that would ahve
customer names and when i click one it would put all there info in the form.
i almost got it working, i have to right click > remove filter/sorting befor
clicking a name in the list box will pull up that prsons info into the form,
how can i make it just automatically pulls up ppl i click on woth ut having
to select that option?
Click to expand...
Click to expand...
 
Happy to help.
First, you will need to create a Combo Box control. If you have the wizard
icon clicked on your tool bar, it will walk you through setting up the
properties you need.
Give it a meaningful name. Using good naming conventions, a combo box
control should start with cbo. Since you are going to look up customers, I
would suggest cboCustomer as a name. I would also suggest you set the Auto
Expand property to Yes. That causes it to follow your typing to the best
match until you see the customer name you want.

Once you have to combo set up, test it to see that, in fact, you get a list
of customers. Now, go back to design view, select the combo, and click on
properties and select Afer Update from the event tab and Code from the Coose
Builder dialog. Paste the code I posted. You will need to make the
following modifications:

Remove these lines, Your Sub name and it's End Sub will already be there:
Private Sub cboActivity_AfterUpdate()
End Sub

Change all occurances of cboActivity to cboCustomer (Or whatever name you use)

Change these lines to meet you needs. For example you may want -
"Add New Customer"...
If MsgBox("Add Activity Number " & Me.cboActivity _
& " To the Attribute Table", _
vbYesNo + vbQuestion + vbDefaultButton2, "Activity Not
Found") _

That should do it. Post back if you have more questions.

ppskidpps said:
Thank you both for such timly responce! Klatuu I'm very interested in what u
had to say! I'm new to access and don't completely understand where to edit
your code u gave me could u mark where and with what for me if its not to
much to ask? thanx!

Klatuu said:
In addition to Al's suggestions, if you are only wanting to find one
customer, I would suggest you consider using a Combo Box control rather than
a List Box control. The Combo control is actually much better at this sort
of thing. It is also more user friendly because it allows them to find a
customer by typing in the name. If you set the combo box property AutoExpand
to Yes, it will follow the user's typing and position on the closest match.
Then when the user has selected a customer, add the following code to the
After Update event of the combo box. This is the most common way to do this.
Note - you will have to change the names in this sample to match the names
in your applcation:

Dim rst As Recordset
On Error GoTo cboActivity_AfterUpdate_Error

If Not IsNull(Me.cboActivity) Then
Set rst = Me.RecordsetClone
'Search the table for the value in the combo
rst.FindFirst "[Activity] = '" & Me.cboActivity & "'"
If rst.NoMatch Then
'Did Not find it in the table
If MsgBox("Add Activity Number " & Me.cboActivity _
& " To the Attribute Table", _
vbYesNo + vbQuestion + vbDefaultButton2, "Activity Not
Found") _
= vbYes Then
'Add a new record to the table
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Else
'Do Not Add a new record, Empty the combo and start over
Me.cboActivity = Null
End If
Else
'Makes the selected record the current record in the form
Me.Bookmark = rst.Bookmark
End If
End If
cboActivity_AfterUpdate_Exit:

On Error Resume Next
rst.Close
Set rst = Nothing
Exit Sub

cboActivity_AfterUpdate_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure cboActivity_AfterUpdate of VBA Document
Form_frmAttributetable"
GoTo cboActivity_AfterUpdate_Exit




ppskidpps said:
I started a database on the access 2002 on the "order entry" template. On the
orm where u place orders i would like to install a list box that would ahve
customer names and when i click one it would put all there info in the form.
i almost got it working, i have to right click > remove filter/sorting befor
clicking a name in the list box will pull up that prsons info into the form,
how can i make it just automatically pulls up ppl i click on woth ut having
to select that option?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top