Hey guys

Another Problem i have is on my form which creates new customer transaction. I can't seem to be able to add parts to a transaction(which is in a seperate table)

This is a small part of the form. I have a transactionID which is an auto number field, PartID (List Box) and quantity boughttextbox.

When i go to submit it, my insert into query is:
INSERT INTO Transaction_Parts (TransactionID, PartID, Quantity) Values (100030,100352, 4);

(I want the Autonumber to also be copied into the other table as just a plain number which is why i listed it in the query. That could be wrong?)

When i go to do this it comes up with.
http://img232.imageshack.us/img232/3889/errorrj8.png

How can i fix this problem?

When i go to submit it, my insert into query is:
INSERT INTO Transaction_Parts (TransactionID, PartID, Quantity) Values (100030,100352, 4);

(I want the Autonumber to also be copied into the other table as just a plain number which is why i listed it in the query. That could be wrong?)


I really don't see a problem with what you are trying to do, so I am just taking a swag that the fields you inserting into are a different data type or a type that can't handle what you are trying to pass. For instance, are the fields in the other table that will receive the correct type?

Other than that, I have not idear.

-dK

Brett,

I don't know about this in a Macro context.

You can not supply the AutoNumber in your Insert statement. Insert the new
row without the AutoNumber, then retrieve it using a function like DMax. The
latest entry will have the Largest value of your AutoNumber field.

Then use the retrieved value to do the Insert into the second table.

Wayne

Wayne brings up a good point which I overlooked. Apologies for my confusion .. let me clarify.

If you are inserting into a table where TransactionID is an autonumber field, it's not going to happen. That is generated automatically.

If you are copying from one table and inserting into another table and want to copy the TransactionID which is an autonumber in the first table and not into an autonumber into the 2nd table, it should work as long as the data type will accept a long type.

If you are trying to insert TransactionID into a field that is the autonumber, then it will not work. The other table should have its own primary key and the TransactionID you want to copy to maintain the link between the tables should be in it's own foreign key field (non-autonumber in the receiving table).

-dK

Just thought i would update you guys. I changed the insert into query so the values were as [forms]![formname]![fieldname] but it still didn't work, however, I ran the insert into query in an sql query view thingy and it popped up with the enter paramater box then copied what i typed into the paramater boxes into the table. So maybe there is a problem in relation to the form there. Like what could it be though...

Brett,

One thing to look for:

When you reference --> [forms]![formname]![fieldname]

There's a real good chance that the REAL NAME of your form control is
something like 'Text16'. You don't want to reference the Control Source.
It's generally a good idea to supply your own name.

Your table knows that [fieldname] is 'TotalHours'
You know that it is 'TotalHours'

But the form doesn't necessarily see it that way.

Wayne