Adding a new record automatically via code

[Benjamin Bolduc]

Hi everyone,

I'm having trouble with the AddNew record method. Access help only has examples of code for user input and I can't seem to find specifically what I'm looking for in the archives.

I have a combobox on a form that is linked to a query that checks for low product inventory. (Basically it just lists all the products whose balance is below the reorder level). I have it set up so that when I double click on one of the items, it sends all of the items on the list with similar suppliers to a premade Purchase Order Report.

Now here's my problem...

I need to create a new record in the "Orders" table for each item BEFORE it is sent to the report. There could be any number of items being selected at a time. The problem is that some of fields that will make the new record are taken right from the forms query and some need to be user input.

Here are some fields from the "Low Inventory" records that need to go into a new record in the "Orders" table:

UPC# - (Primary Key, This will be entered automatically)
Quantity Ordered- (This could be different on every order, so it needs user input)

After, I'll base the report on the new record so Ill have a functional Purchase Order. I can do all that, I just need help with the AddNew syntax. Maybe a Do-Loop is necessary? I don't know, that's why I'm asking you wonderful people

Any help you guys could give me would be greatly appreciated. If you need me to clarify anything, please don't hesitate to ask.

Thanks!
Ben

 

[Travis]

Ben,

Have you given thought to having the data appended to a Blank Temporary Table?

You could then open up a form to allow for the user input and then append this information to the orders table. By using this method you would be able to use an Append Query to move the similar items into the Temporary Table and then another Append Query to move them into the Order Table. This would eliminate the need to cycle thru each item to get the Quantity on order.

 

[Benjamin Bolduc]

That sounds like a much more efficient way of doing it. I've never used Apend queries before though. Do I create them in code? or use a wizard and save it?

Also, do I use the CreateTableDef Method to make the temporary table. I'm not too experienced with these particular things, so thanks for your patience.

And thanks for your help!
-Ben

 

[Travis]

You can create a table each time or just set up a table with the fields that you want and create a "Delete" Query to clean it out when your done with the data that is in it.

You can create Append Queries or Use SQL Strings and the CurrentDB.Execute method to run them.

Since you have not used the Append Query before, I recommend that you first create the Table that you will use to temporarily hold the Order Data and use the QBE Grid/Wizard to help you get the hang of the Syntax.

 

[Benjamin Bolduc]

Ok, lets see if I understand the process correctly:

First, I click on an item in the combobox on my "Order Check" Form. This sends all items with similar suppliers to a premade table.

A form pops up that is based on this table where I can change the records as I need.

Then the updated records are sent to an append query that adds them to my "Orders" table.

Then a Delete Query clears the premade table and my purchase order report opens filtered to the new records.


Does this process sound feasible? Thanks again for your help

-Ben

 

[Travis]

Not only feasible, but that is what I have been pointing your towards.

 

[Benjamin Bolduc]

Great!
Now all I have to do is figure out how to actually do these things. Oh well, looks like up for another learning experience