Search records message box

[miss031]

I apologize if this has been covered somehwre else. I was not sure what terms
under which to search.

I am creating an auction sale database. I have created table and form for
bidders information, a table and form for entering for their purchases, and a
table for the sellers of the items.

When the bidders register, their information is entered into the form, and
they are assigned a number with which to bid, which is the primary key for
the bidder registration table.
When items are sold, they are entered into another form, with the bidder
number identified for each item.

The cashier has a form with a sub form. Form shows "bidder number", "name",
"phone number", "notes"; the subform shows a list of all of this bidder's
purchases.

There is a button on the form which, when clicked, brings up the report of
the bidder's purchases, totalled with taxes applied.

Questions are:
1. How can the cashier enter the bidder number or the phone number, and look
up the record that corresponds? Not looking up by the database record number
(ie record number 1,2,3), but by the contents of the field ("bidder
number=365" or "phone number=555-2435"), not using "Lookup Wizard".
2. How, when clicking the button that prints the report, can the cashier
print only the report for that bidder number?

The ideal scenario would have a message box that simply asks "Enter bidder
number or phone number" with a space for each, and then opens the formwith
subform showing that record.

Have I got everybody thoroughly confused by now?

 

[tina]

rather than a "message box" (that would be an Inputbox, actually), suggest
you build an unbound form, which i'll call frmSelect, to act as an input
box. add two unbound textbox controls, for the bidder number and phone
number values - i'll call them txtBidder and txtPhone. also add a commmand
button, which i'll call cmdOpen.

set the RecordSource of your existing form (which has the subform on it) to
a query with all the fields you need for the form, and include the following
criteria on the bidder number and phone number fields, respectively, as

[Forms]![frmSelect]![txtBidder] Or [Forms]![frmSelect]![txtBidder] Is Null

(the above goes all on one line, regardless of how it's line-wrapped in this
post)
and

[Forms]![frmSelect]![txtPhone] Or [Forms]![frmSelect]![txtPhone] Is Null

(again, all on one line)

add code to cmdOpen to open the form, as

DoCmd.OpenForm "name of form"

to open a report that shows only the mainform record, you can add a WHERE
clause to the OpenReport action, to restrict the table or query that the
report is bound to. read up on the OpenReport Action topic in VBA Help to
understand how the arguments work, and post back if you have any problems
with it.

hth

 

[Designing-Systems.com]

hi,

you can use a input prompt and get the bidder number as an input. Use this
number in your query to search the record

SELECT bidder FROM bidder-table WHERE bidder-ID=bidder-number-entered


associate this query with a form to display the bidder information and
similar query in a sub form to display the items.

Regards,

 

[Lookup value from form vs table column]

Does the form link the registration data with the order data? If so, piggy
backing off of the previous selection, you could have an inquiry form (locked
so no input is allowed) and filter for either the phone # or the bidder
number.

 

[Tim Ferguson]

SELECT bidder
FROM bidder-table
WHERE bidder-ID=bidder-number-entered

I think the minus signs will cause problems for the engine, unless you wrap
all the names up in [brackets].


Tim F

 

[miss031]

I got it now.

To find bid number:

Private Sub cbofindbybidnumber_AfterUpdate() ' "Find Bid #" combo box
Dim content
content = Trim(cbofindbybidnumber) & "*"
content = "bidder_bidnumber like '" & content & "'"
If cbofindbybidnumber <> "" Then
Me.Recordset.FindFirst content
' finds a particular bidder, and displays their information and
purchases on the form
End If
End Sub


To print individual invoice:

Private Sub print_invoice_Click()
On Error GoTo Err_print_invoice_Click

Dim stDocName As String
Dim strWhere As String 'added

stDocName = "Item Invoice"
If Not IsNull(Me.cbofindbybidnumber) Then
strWhere = "bidder_bidnumber = " & Me.cbofindbybidnumber
End If
DoCmd.OpenReport "Item Invoice", acViewPreview, , strWhere

Exit_print_invoice_Click:
Exit Sub

Err_print_invoice_Click:
MsgBox Err.Description
Resume Exit_print_invoice_Click

End Sub