From that makes Multiple records

[sgp667]

Hi guys my name is Thomas and I am pretty new to access, and this forum as well. I am not sure if this question belongs in this section of the forum but, I was asked to make database for managing company's supplies. For this data base I need a form that will produce multiple entries on two tables(trust me if I would be the only person using this database I would make two forms for this, but people that will be involved are not big computer fans so I need to simplify things for them).

So the form I am making is for inputting Invoice items where user will have to just input item name, item type and their quantities. The result of most entries is simply a new record in "InviceItems" table. However when user inputs specific item type( in this case "Machine"), then I need a new record to appear on another table as well("MachineList"). ALSO if the quantity of Machines inputted by user should correspond to amount of new entries made in "MachineList" table.



I should note that i am not familiar with Macros, or VBA but if anyone can provide me with specific script I would be happy to take it.

[June7]

This sounds like duplication of data, which is counter to principle of relational database to not duplicate data. Want to provide db for analysis? Follow instructions at bottom of my post.

[sgp667]

I'm sorry but I'm unable to shrink the db under 1.01mb (uploader reuquires me to have 500kb at most).

So let me explain one this a little bit better.

Currently I have three tables(fields in these tables are in brackets), and one Form:

Invoice Items [ItemName, ItemType, Quantity, Price, Invoice#]
-list of items that appear on our Invoices
-this is where every entry on my Invoice Form appears
-this is used for queries that will count our purchases

Cleaning Assets List [ItemName, ItemType]
-this is just a list items used by company
-this is used to limit items that user can put in Invoice Items Form

Machine List [MachineID, ItemName, Invoice#]
-it is a list of individual machines rather than Machine types we use( Machine Types can be found in "Cleaning Assets List"
-when a new machine(s) appear on Invoice a new entry has to be made here( or more depending on Quantity inputted in row with an [ItemType] "Machine")

Invoice Form
-this is the form where user will put items that appear on our invoices
-this is also the form that needs to make multiple entries in MachineList


I guess I should stress that "Cleaning Assets List" is just a list of items company allows us to buy( thus have on invoice), and "Machine List" is more detailed because it actually lists machines one by one( to keep track of their status exp. Broken, Available, InRepair ect.).

[June7]

Still don't follow the setup.

Per my instructions, zip the file, 2mb allowed.

[sgp667]

Ok I found my mistake from yesterday
Database sample.zip

This File has only the components in the database that i stated before.

Thanks for the help

[June7]

Think understand now. You buy 5 items (MM Vacuum) on invoice 111. You want a record in Machine Archive for each of the 5 items to track equipment asset. Need to add field for 'Status'?

If you want code to automate adding the 5 records, will involve INSERT sql action. The code itself is not that difficult. What gets tricky is figuring out what event to put the code in and have measures to make sure the items are not posted to the table multiple times.

Simple code example:

Dim i As Integer
If Me!ItemType = "Machine" Then
For i = 1 to Me!Quantity
CurrentDB.Execute "INSERT INTO [Machine Archive]([Item Name], Invoice) VALUES '" & Me![Item Name] & "', '" & Me![Invoice#] & "'")
Next
End If

Invoice# in InvoiceItems is number type (should it be autonumber?) but the Invoice field in MachineArchive is text. These fields should be same datatype if you want to be able join in query.

Advise not to use special characters and spaces in names nor reserved words as names. If you do, must in enclose in [].

[sgp667]

Ok this code does look promising, I was doing a little bit of research as to where can I paste it, and so far all I got is Query.
I guess if it possible I would execute this when pressing save button on my form.

You are right about the invoice field it was just an error, also the Machines will have status, but my plan is to have a "Status Event" table so that I would determine each machine's status by most recent status event.

[June7]

Yes, could put code in button 'Save', however, if 'Save' is clicked again another set of records will be saved. Be careful with offering this kind of feature to users.

Don't understand 'and so far all I got is Query'.

[sgp667]

I finally embedded the code it works great.

Thanks