Lyn,
it is a bit clearer now that we know a bit about the tables and what you
want to do.
From your description I think each lot-no of plants will only have one
winning bid.
This is the type of setup where all the info goes into one table.
table for winning bids
---------------------
tblWinningBids
BidID >> Primary Key (autonumber)
lot_no
PlantDesc
BidderLastName
BidderFirstName
TransactionNo
Unfortunately I don't know anything else about what you want to do.
I will just assume that there is only one table needed for this purpose.
For tracking winning bids for lot-no's you just need one table because each
lot_no will only have one winning bid
If you have only one table, you don't need a subform
Note: If lot_no is a number field, clear the 0 from its default view (lower
pane of table), same for TransactionNo
Use the form wizard to create the data entry form
In the wizard choose tblWinningBids from the dropdown for Tables/Queries
--Choose all the available fields
--Choose columnar for layout
--Choose a style
--name it frmWinningBids
--Click Finish
The form will open.
Switch to design view
--select the textbox for BidID and set its visible property to No on the
property dialog
--delete the textbox for Lot_no
--on the toolbox make sure the wizard button is clicked on
--click the combobox tool and then click and drag on the form to make a
rectangle
--choose the third option - find a record
--choose lot_no
--continue to finish
Make a new form just to enter lot_no and plantDescriptions info, base it on
tblWinningBids
--form wizard
--choose tblWinningBids
--choose BidID, lot_no, PlantDescr for the fields
--click Next, choose columnar
--choose a style
--name it frmAddLotNo
--finish
The form will open.
Switch to design view
--select the textbox for BidID and set its visible property to No on the
property dialog
--save and close the form
Open frmWinningBids in design view
--on the right side of the combo for choosing lot_no, put a label with the
words add lot_no
--set the text colour to blue
--click once on the label and in the property dialog on the events tab,
choose On Click by clicking the button with the ellipsis (.)
--choose Code builder from the dialog that opens
--the VBA window opens
--type the following on the blank line between Private Sub label14_Click
(your label will have a different number from 14) and End Sub
DoCmd.OpenForm "frmAddLotNo"
--save and compile
--close the VBA editor
--on the form still in design view, select the combo for lot_no
--on its property dialog on the events tab, choose On Enter by clicking the
button with the ellipsis (.)
--choose Code builder from the dialog that opens
--the VBA window opens
--type the following on the blank line between Private Sub Combo12_Enter()
And End Sub (your combo will have a different number from 12)
On Error Resume Next
Me.Combo12.Requery
--Replace Combo12 with the exact name of your combo for the line immediately
above this one
--save and close the form
Now you will be able to add new lot-no's and also add the winning bids from
one form
Jeanette Cunningham
Lynlongley said:
I have two tables:
(plant_info) has information on the plants up for auction. this data is
shown in the subform. Each plant up for auction has a lot number [lot].
(bidding_data) records the price that the [lot no] sold for, the person
who
won the plant, and a transaction number.
On the main form, I enter the [lot no] by typing in a box. The the
transaction number is automatically generated by some code when this box
is
updated. I also want the subform (based on the [plant_info] table) to
update.
I took your advice and added a combo box in the header of the main form.
When I choose the lot number, it updates the main form, but the subform
doesn't update.
Jeanette Cunningham said:
Lyn,
not sure what you are trying to do - when we understand, can probablt
give
you a better way to do it.
Are you trying to enter the lot value and then read the details for that
lot
in the subform?
If you are, people usually use a combo for this and then do a FindFirst.
If this is what you want to do, we can give you the code or we can tell
you
how to do this with the form wizard.
Jeanette Cunningham
Lynlongley said:
I want to update the subform (form for auction subform 3 plant picture)
as
soon as I enter a number in the "lot" field in the main form (1_form
for
auction)
I have written the following code:
Private Sub lot_AfterUpdate()
Forms![1_Form For Auction]![form for auction subform 3 plant
picture].Form.Requery
End Sub
It will update the subform, but with the number from the previous
record!
If I hit F9, then it updates the subforms correctly. What am I doing
wrong?
