Invoice - Starting the flow 1

[mclellan]

Hello,

I am planning on having an invoice form with a sub form (to hold the detail lines) to process customer requests.

To get the ball rolling, I am envisioning a box in the top right corner of the form where I will pick a customer name either from a drop down, or I will enter in a phone number and click a button to get the rest of the customer details.

Once I click this button, I am hoping to have all of their old invoices show up, and then give them a button to add a new record.

Does anyone here have a good reference on how I should start? If I setup a query for the customer name, how do I then back fill the data into the rest of the form to start processing?

If anyone has a sample database that is close to this, I would love to have a link!!

Thanks
Barry

 

[IGPCS]

well first dont use a button use the code on the customer name after update then you shoud use a combo box either by phone or name dosnt matter but make invisible columns in the combo for all the customers information add empty bound or unbound text boxes where you will insert the information from the combo like this

Address1 = ComboBox1.Column (2)

assuming that column 3 in the combo box has the address in it then the query from the old ivoices base on the customerid that you chosen (type this in the criteria) FormInvoice!CustomerID

hope you get the idea

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[mclellan]

Thanks IGPCS,

now to learn about unbound text boxes!


Barry

 

[mclellan]

ok, this is MUCH harder than I thought!

sorry IGPCS, could you try explaining it one more time.


Thanks!
Barry

 

[IGPCS]

please let me know exactly what you have on your forms and will be glad to help you all the way till you get it

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[mclellan]

Thanks IGPCS

It's really kind of sad, I just can't figure out how the logical links would work between my selections.

Here is what I have so far:
unbound form
in the form, I setup a customer field and after I tab off of this field, it executes a query based on what I have inputted. I haven't got further on this because I am not sure how I would populate the other 'customer contact' fields... i.e whether I need VBA code after update to say, got the record, now set the values of the other fields to what was returned from the query.

I then setup some other fields which would be eventually used as my invoices, however I am not sure how to link them (based on Customer Phone) to actually grab the data, after this, it would at some point feed into a sub form with line items.

Unfortunately I can't even find any examples on how I can link something like this. I know I can make a form bound to an 'invoice' table and then set the sub level form bound to a field in the main form, but that's about it!

Thanks for your help
Barry

 

[IGPCS]

now i do have a question why is the form Unbound??

you should have minimum 3 tables

Customers 'Customer basic Information (i.e. Name Phone, Address)
Invoices 'Basic Invoice Info (i.e. InvoiceNo, Date, CustomerID)
InvoiceDetails (i.e. InvNO, ItemNO, ProductName, Price, QTY)

and then you need to create a form that is bound to the Invoice and a combo box there that has the customers Info including CustomerID and
on that form 2 subforms 1 Bound to InvoiceDetails that is joined to the mainform via InvNO

then a second subform Based on a query that will take the invoices and should have also the field of CustomerID joined to the mainform via customerID and after you choose a customer from the ComboBox it should show all the invoices that belongs only to this customer

hope you understand me

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[mclellan]

Thanks IGPCS!

Ok, still not a lot of luck, but I think I am close!

I have created a blank form bound to the Invoice Table.

I setup a combo box which returns all fields from the Customer table (including CustomerID)

I put a subform for the Invoice Details and linked it to the main form based on the InvoiceID field

I have written a query that takes the CustomerID parameter and returns everything in the Invoice table

Now the hard part, I am not sure how to add the second sub-form which says:
Take the Customer ID from the combo box, pass that as a paremeter into the Invoice Query and set the InvoiceID on the main form to feed the invoice detail subform.


Thanks!
Barry

 

[IGPCS]

well create the subform based on the query that gives you all the invoices that are this custoemrs that youget by the Customer ID from the main form and linked

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY