Quick Question..

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

Guest

I have a form named 'Update Purchase Orders', but I want it to display the
purchase order that the user specificaly requires - and no other records.

I wondered what would be the best way to achieve this.
I was thinking of editing the forms onOpen event to contain something like:

Dim POid As String
InputBox "Enter PO ID" = POid
but how would I be able to display only the PO number entered?

Any suggestions would be appreciated
Thank You,
Kenny
 
Ken said:
I have a form named 'Update Purchase Orders', but I want it to display the
purchase order that the user specificaly requires - and no other records.

I wondered what would be the best way to achieve this.
I was thinking of editing the forms onOpen event to contain something like:

Dim POid As String
InputBox "Enter PO ID" = POid
but how would I be able to display only the PO number entered?

Any suggestions would be appreciated
Click to expand...

What I would do...

Open the form initially with a filter guaranteed to return zero records (where 1
= 0 for example).

Add an unbound ComboBox at the top of the form labeled "Go To..." and in its
AfterUpdate event apply a new filter to display that record.

Me.Filter = "POid = " & Me.ComboBoxName
Me.FilterOn = True
 
Input boxes don't give the user a list of valid choices, and you have to
write code to manage the possibility (probability) of mis-types. Try this
instead:

In the form's header, make an UNBOUND combo box whose row source is the list
of PO's (let's call that box POChoice). In the form's RecordSource query, put
this criteria for the PO field: =[Forms]![yourFormName]![POChoice]. Finally,
in the POChoice_AfterUpdate, requery the form: Me.Requery.

When the form opens, POChoice will be null, so the form will show only those
records where PO# is null (presumably no records). As soon as the user picks
a PO, it will appear in the form's detail section.
 
Rick,

Can you tell me if it is more efficient (at runtime) to make & turn on the
filter, or to embed a reference to the control in the criteria of the form's
RecordSource?

I have been using the latter, but that's only because I don't know any
better (I learned the query builder before I got into VBA), and I could be
doing this the Neanderthal way...
 
Brian said:
Rick,

Can you tell me if it is more efficient (at runtime) to make & turn
on the filter, or to embed a reference to the control in the criteria
of the form's RecordSource?

I have been using the latter, but that's only because I don't know any
better (I learned the query builder before I got into VBA), and I
could be doing this the Neanderthal way...
Click to expand...

I really don't know. The filter method is more flexible because you can
"remove all filters" or apply filters that don't use the ComboBox whereas
using the ComboBox in the query criteria "locks you in" unless you want to
modify the RecordSource of the form.
 
The sub below takes the value from an unbound text box (txtGotoRecord)
and alters the Filter applied to the form to jump to the record number
requested. I've found it quite usefull and you may want to play with
using it as-is. Others take the value returned from the InputBox()
function and substitute it for the
[Forms]![frmReservations]![txtGotoRecord] statement. I would suggest
that you test the value returned using a DLOOKUP() to confirm that a
valid PO number was entered. Also, you'll need to figure out when the
code should execute. The nice thing about the my implementation of it is
that it just sits on the form waiting for a value.

Private Sub txtGotoRecord_AfterUpdate()

Me.FilterOn = False
Me.Filter = "lngTransportId = " &
[Forms]![frmReservations]![txtGotoRecord]
Me.FilterOn = True

End Sub
 
Back
Top