Listbox dropdown

FreddieLeaf

Registered User.
Local time
Today, 12:07
Joined
Apr 21, 2006
Messages
17
I'm pulling my hair out. I'm making a seemingly simple tex book database.

I have 2 tables. AuctionItems and AuctionBidders. I have three forms. One to enter AuctionItems, one to enter AuctionBidders, and one to enter Transactions. The AuctionItem Table has a column for the winning bidders ID WinningBidderID.

On the Transaction form I list the acution item data and also want to put a listbox that allows the user to choosed the AuctionBidder that won this auction.

I cant figure out how to make this listbox. IIn the listbox dropdown, I want it to show the Bidders last name, first name, etc in the listbox, but then store only that persons BidderID in the AuctionItems table field WinningBidderID. I also want to display the bidders information on this form once selected. I cant figure this these things! Its driving me crazy.

Can someone give me some help or link me to an example database?

Thanks!:eek: :eek:
 
I'm not saying a listbox is what I'd choose, but this should get you started...
-Turn on the control wizard on your toolbox menu.
-Put a new listbox on your form.
-On the first page of the wizard, select 'look up values in table....'
-Select the fields you want to SEE in your listbox. (no need to select PK)
-Skip ahead to where you see 'store the value in this field...'
-Select WinningBidderID or whatever..
(Take a look at the properties for the new listbox...esp that the Key column came into the rowsource even though you didn't pick it.)

Once you get this to work the way you want, you can put the rest of the bidder info in a subform or whatever.

Need more help?
 
- create a query: qryBidders. include BidderID (first), then (in any order) the last name, first name, etc.
- your Transaction form's record source should be tblAuctionItems (or a query of that table)
- put the WinningBidderID field on the form
- change the WinningBidderID-control to a combobox and make qryBidders the RowSource of the box.
- for column widths of the combobox, make the first column 0cm/in. this will hide the ID - but it will still be there!
- since the ID is in the first column, IT will be stored as you want.

hth.
 
oops, a little late...
 
Sergeant said:
I'm not saying a listbox is what I'd choose, but this should get you started...
-Turn on the control wizard on your toolbox menu.
-Put a new listbox on your form.
-On the first page of the wizard, select 'look up values in table....'
-Select the fields you want to SEE in your listbox. (no need to select PK)
-Skip ahead to where you see 'store the value in this field...'
-Select WinningBidderID or whatever..
(Take a look at the properties for the new listbox...esp that the Key column came into the rowsource even though you didn't pick it.)

Once you get this to work the way you want, you can put the rest of the bidder info in a subform or whatever.

Need more help?

Thank you, Thank you, Thank you!! Finally a breakthrough and I don't have to throw my laptop on the ground and smash it into a billion pieces. (just kidding)

Sergeant, I was able to do this the way you described. I now have a listbox on the form. I can select the Winning Bidder from the dropdown list and Access puts the WinnerID into the AuctionItems table. It's not very ellagant thought. Maybe that's why you said you would not do it this way. There is not a null space in the list so once you go into the list, you have to select a WinningBidder, even if you were just browsing. I also can't figure out how to display the WinningBidder information via the WinningBidderID stored in the AuctionItems Table (the one you just showed me how to do).

Sergeant, what other way would you suggest going this instead?

Thanks.
 
We might have a mismatch of terminology here. A listbox has no 'dropdown'. When you said you wanted a listbox, that's what I helped with.

What would I do? That would depend on the scope of the project.
You can use a combobox on your main form, made with the wizard as I described above. Then you can make a bidder form and drag that onto your main form as a subform. Relate the subform child field to the main form master field in the Properties/Data window for the subform.
 
Sergeant, That's it!! I get it now. Thanks so much. I think I'm making this too complicated or something. :D :D

Here is what I did:I made a main form with the auction item data on it. Then added a combobox that has a dropdown for selecting the bidder. Once the bidder is selected, the form dumps the bidderID to a textbox on the main form. Then, the subform picks off the bidderID from the text box and displays all the bidder data on the subform. wow, its actually working. If this is the most efficient way to do this I'll keep it.

Now, one last thing. I need to print a receipt :) I does not need to be fancy, just need to dump some of the data from the textboxes to a single sheet of paper for each auction item- Winning Bidder Name/Address, Item description, calculate some tax, etc. I thought I could setup a button on the main form, when pressed, the reciept is printed. Any staring point ideas would help.

Thanks!!!!
 
How about a report? You can customize it to look however you want. The next thing you'll want to know is how to open a report to a certain record (from a button on the form)...
Give it a shot, and if you need help, let us know.
 
I'll try the report thing and let you know if I run into any road blocks. Thanks for all your help, it's very much apprciated.
 
Last edited:
Ok, I'm a little stuck here. I created a Report that has all the Fields I need displayed on the Receipt. When the "Print Recipt" button on the AuctionID form is pressed, it needs to pass the AuctionID to this Report. I'm not sure of the method.

I'm thinking that I need the following, but maybe I'm overcomplicating this again:
1) "Print Receipt" Button on the main form that passes the AuctionItemID to the Report. Some VBA Code maybe??
2) Report that formats the Reciept, showing the data that needs to be printed as pulled from a Query.
3) A Query that pulls only the data for this one AuctionItemID.

:confused: ...again.....
 
1. Make a query for your report.
2. Under the 'AuctionID' field in the query, in the criteria block...right click and select 'build'...in the list on the left side of the dialog, navigate to your form and select the AuctionID field from the middle.
3. In your report, select the query as your data source.
 
Sergeant,

Once again, you pulled through for me. I created the query like you said and it works like a champ. I'm not exactly sure why it works, but that's not important right now. :)

A few more loose ends...

1) I need some help with an Expression Script. On the query form, I need to calculate tax only if the table has a TRUE in the Taxable field. I figure I would add an express to the TaxAmount TextBox that calculates and shows the tax? Is this the the place to put it? I think it would look something like this, can you help with the syntax? if Taxable = TRUE then (ItemCost * taxRate)

2) How do you make the button on the form call the query and pass it the item you want to display. So the user does not have to type it into the popup box?

BTW, five "how to make an Access database" books from the local library and 20+ hours of tinkering with Access were not nearly as helpful as your suggestions. Sometimes you just need a small push to get going. I'm actually learning alot more about Access thatn I bargained for. I now have a mostly working database now!!!! I'm afraid it's going to get me a reputation that "I'm good with Access" and everyone is going to ask me to make a custom database for them. I guess that's not all that bad. :cool: Thanks!!
 
Last edited:
What I would do with the tax is this:
-Forget about the 'Taxable' yes/no field.
-Store the TaxRate in the transaction table (wherever the ItemCost lives).
-You can enter 0 or whatever the tax rate is in the TaxRate field.
-Wherever you are pulling the sale data, just use ItemCost * TaxRate.

You don't have to do it that way, just what I'd do.

See if you can figure out how to apply the tax in the form using a button or toggle. (You're going to have to write some VBA logic in the form_current event and in the button_click event.)
 
I did a combination of both ideas. I calculate the tax on the fly in the query report by putting an expression in the Tax Collect field like this. Taxable is a bit Yes/No. Interestingly enough, for it to work porperly I had to multiply the expression by -1. the only thing I can think of is that a TRUE is represented by a -1??

=[Taxable]*[WinningBidAmout]*0.06*-1
 
Cool.
Yes, True = -1 in Access.
You did marvelously...don't tell anyone that you 'know' Access...it will never end!
 
Sergeant,
The cat is already out of the bag. The "Auction Committee" has now seen the database. I could see their wheel spinning for future project.... :rolleyes:

Everything is looking great on the database! I could not have done it so quickly without your help. Thanks for keeping up with my questions. It is very kind of you!!!
 

Users who are viewing this thread

Back
Top Bottom