Newbie needs help with a form

[Biged5oh]

Hello all!



I am attempting to create a database that will serve as an invoice creator for our organization's car wash expense.

I have a form, that will populate a table where each car wash will be documented. When you enter the car number, the make year and size of the car automatically populate those fields.

When you select the vendor, the address and city automatically populate.

What I am having trouble with is once the car size is determined, and the vendor selected, I would like the form to automatically calculate the price according to the price schedule set up in the vendor table (Sedan price vs SUV price vs Van price, and its different for each vendor). What type of approach should I be looking at?

I have tried the If ElseIf expression, but i cannot get it to select the value within the vendor table.

All help appreciated! Im not well versed in VBa, but I have been able to copy and paste some code.

[pbaldy]

I'm guessing the vendor table has fields for the different sizes? In a normalized database, they would be in a separate table. It would have fields for vendor, size and price (and probably its own autonumber primary key field). Then you have a simple lookup with vendor and size as criteria.

[Biged5oh]

Yes, the vendor table list each vendor with their price for Sedan, SUVs, and Vans in separate columns.

So what my challenge is, on the user input form, I want to limit to just pull down menus no room for freestyle input. They select the vehicle from the list of the fleet, they select their names from a list of personnel, and they select the vendor from the list of vendors. But I would like the form to automatically select the proper price from the vendor table, after the vehicle is selected (size is known) and vendor is selected. Is it even possible to have the form perform this? or should I be looking at a separate query after the user inputs data?

[pbaldy]

It's possible, just easier if the tables are normalized. If you want to stay with your design (what happens when there's a new size?), attach the db here and we can figure out the required code.

[Biged5oh]

I cant for the life of me , get this DB down to under 500 kb! Is there a trick I should be using to be able to upload?

[pbaldy]

If you compact/repair and then zip you may be under the 2 meg zipped limit.

[Biged5oh]

From the sample attached, what I am trying to do in the SLIPS form is for the VFEE amount to automatically populate according to the VSIZE value and the Vendor. Any help id greatly appreciated!



Car Wash samp.zipCar Wash samp.zip

[pbaldy]

Your fields don't match up, you have "Passenger" in the car wash table but "Sedan" in the vehicle table. Is anything not an SUV or VAN considered passenger?

[Biged5oh]

yes, anything not and SUV or VAN is a SEDAN (Passenger)

[pbaldy]

Here's a start. I cut out the code setting the value of the size variable so you could have some fun too (why SUV in one table and SUVs in the other?).

    Dim rs                    As DAO.Recordset
    Dim strSQL                As String
    Dim strSize               As String

'set size variable here

    Set db = CurrentDb()
    strSQL = "SELECT " & strSize _
           & " FROM CarWash " _
           & "WHERE ID = " & Me.cboVENDOR


    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    If Not rs.EOF Then
        Me.txtVFEE = rs(0)
    End If


    rs.Close
    Set rs = Nothing
    Set db = Nothing

[pbaldy]

And I'll reiterate that the proper design is a table with

Vendor...Size...Price

You shouldn't have to redesign the database next month when they decide to have another special price for trucks.

[Biged5oh]

Thanks Paul, but I cant seem to make that work. I did take your suggestion to fix the design a bit.
So I added a vendor fee table as follows:


and changed the relationships as follows:


Here is the Slip Form


So When the user opens the Slip Form, there's an auto number serial, and the present date is filled in.
The VEHICLENUM field is a pull down menu. When the user makes a selection the VYR, VMAKE, VMODEL, VPLATE, VZONE, VUNIT, VSIZE are automatically populated.
When user makes OTAX selection, the ORNK, OLNAME, and OASSIGN fields are automatically populated.
When user makes VENDOR selection is made, the VADDRESS, VCITY fields are populated.
What I would like to happen next is the VFEE field is then auto populated based on the VSIZE field and the VENDOR field selection.

The intention of the form will be once the user completes the form, it will be printed as a voucher for use at the vendor's locations. Our side will provide us with a table of printed vouchers that we can use to account for expenses and create invoices for payments.

[pbaldy]

With that table (though I would store vendor ID instead of name) you have a simple lookup. You can use a recordset or DLookup() with vendor and size as criteria.

http://theaccessweb.com/general/gen0018.htm

[Biged5oh]

So after a long adventure (learning lots along the way), I think I finally came to understand your simple lookup suggestion. I ended up running a lookup based on a query that utilized the vehicle size selected on the form. SO when the user selects the vehicle, it automatically populated the size of the selected vehicle. Then a query based on what the size of the vehicle is, brings up all the vendors, but only their price for said size of vehicle. So all the user needs to do is select their vendor of choice and the price is automatically assigned to the record. Thanks for you help Pbaldy! It took me a while to come full circle, but in the end it was your suggestion that got me past that hurdle. Now onto the next hurdles!

[pbaldy]

Happy to help!