Access form, search for a record from another table and insert into field in current form

Question

I have a table that stores invoices and a form based off of that table that looks like an invoice so that you can search previous invoices. Currently I am creating the invoices in excel and then exporting the data as a new record into the Invoice table. I was wondering if there was a way I could create the invoices from my Invoice form. I have a table of all of the products and prices we carry that would used for the invoice. Is there a way to put a search button on the invoice form that would search for a record or "product" in the product table and insert it into the fields of a new invoice? I have looked a some code to connect to the products table and insert it into fields in the invoice form, but how do I search as well? Any thought or ideas? Any help would be much appreciated!!

Answer 1

Not sure this is the right design. Do you only invoice one product at a time?

Also, it doesn't sound as if you are thinking in terms of primary keys.

Nor does it sound as if you thinking "relationally".

Let me clarify. In most invoicing systems, there is usually an Invoices table, then an InvoiceItems table. The relationship between the invoice items and the invoice would be one invoice to (potentially but not necessarily) many items.

Then you have your Products table. The relationship between the Products and InvoiceItems is one product to many InvoiceItems. Ie, you can sell a given product to many different customers.

So we have:

Invoices -->  InvoiceItems
Products -->  InvoiceItems

Now you need to implement this in your forms.

Matching your tables and their relationships, you create an InvoiceForm and you create an InvoiceItemsSubForm. The InvoiceItemsSubForm, to hold many items, is a continuous form.

In the InvoiceItemsSubForm, you will have a field that takes the Primary Key of the Product (along with other fields such as date, number of products purchased, etc).

To search for your products, you can have a combo box that has the ProductId and ProductName fields. This combo box serves two purposes: it allows you to search for your products, and it allows you to input the product you want in your InvoiceItem record.

If I have misunderstood your question, my apologies. If I haven't misunderstood your question, you have a lot to learn...

Answer 2

Handling this through VBA and control events is typically the best choice:

1. Use an unbound text box in your form as your search input field. This will allow the user to input some text into a box and hit enter, or click some related "GO" button if you choose to perform some sort of search.
2. Set the `OnChange` property of this text box control to the name of the macro or method you designed to handle this event (or click event property if you've instead set up a button to a) check the textbox value; and b) handle it; typically allowing for both the user pressing enter or clicking the button to perform this action). You may also simply call an `inputbox` from some button click event to pop up a prompt to allow the user to enter the product value to search for.
3. Within your handling script, use DLookup() to use that user-supplied value against your products table to get some meaningful product value back which you can then place anywhere on your form.

Also, although I'm not familiar with your particular setup, it seems as though you should ditch your excel file and go with a direct user interfacing MS Access input form. You may find it always easier to deal with user input directly rather than indirectly through oustide files. That way, you have better control over user input validation and user feedback vs fearing whatever the Excel client feels like putting in those spreadsheets.