Invoice ID expression

[exceii]

Hey,

I'm having a problem running an UPDATE query using an expression as the value to update to.

I have 2 tables called "Invoices" and "Orders". Both "Invoices" and "Orders" have a field called InvoiceID with the primary key and foreign key respectively. I have a control "Create invoice" which when clicked creates a new record in the "Invoices" table with the "InvoiceID" being auto incremented. I have a query "InvoiceIDMaxQ" which finds the maximum of the values in "InvoiceID" in "Invoices" which is aliased "InvoiceIDMax". I have a query "OrdersToInvoice" which finds all the "InvoiceID" field records in "Orders" with the value of "1" (that is the default value I have set for that field when a new record is created). I have an UPDATE query which uses "OrdersToInvoice" query and SET's the "InvoiceID" value to [InvoiceIDMaxQ].[InvoiceIDMax].

This does not work and Access asks for a parameter using a parameter popup. Can anyone tell me what I can do to get this to work or have any suggestions on a better way to go about creating Invoices.

Thanks.

 

[jdraw]

There is a free data model here that might be of some help
http://www.databaseanswers.org/data_models/customers_and_invoices/index.htm

 

[penguino29]

Hi exceii,

If a query is asking for a parameter, this usually means that one of your fields is referring to something that the query does't know. I think [InvoiceIDMaxQ].[InvoiceIDMax] is in doubt.

As an experiment, if you change [InvoiceIDMaxQ].[InvoiceIDMax] to a value of "999", I think that query will run without asking you for a parameter.

If you don't mind, perhaps you could use the DMax function to lookup the max ID in the invoice table and use that instead of [InvoiceIDMaxQ].[InvoiceIDMax].

This will save the complication of the additional [InvoiceIDMaxQ] query - which I suspect that it has not been executed and the data was unavailable, and was the main reason why the query asked you for a parameter and failed.

 

[exceii]

Thanks for the link. Its a little complex for my needs though.

 

[exceii]

Hi exceii,

If a query is asking for a parameter, this usually means that one of your fields is referring to something that the query does't know. I think [InvoiceIDMaxQ].[InvoiceIDMax] is in doubt.

As an experiment, if you change [InvoiceIDMaxQ].[InvoiceIDMax] to a value of "999", I think that query will run without asking you for a parameter.

If you don't mind, perhaps you could use the DMax function to lookup the max ID in the invoice table and use that instead of [InvoiceIDMaxQ].[InvoiceIDMax].

This will save the complication of the additional [InvoiceIDMaxQ] query - which I suspect that it has not been executed and the data was unavailable, and was the main reason why the query asked you for a parameter and failed.

Thanks for the advice. You are right, when I change the updade field to a hard coded value it works. Unfortunatly when I use the Dmax function it throws a conversion error. I tried to run it though the CInt fuction but it still threw out the same error. I think this is because I'm getting a value from an AutoNumber field and trying to assign the value to a number field. I'm at a loss.

 

[exceii]

Thanks for the advice. You are right, when I change the updade field to a hard coded value it works. Unfortunatly when I use the Dmax function it throws a conversion error. I tried to run it though the CInt fuction but it still threw out the same error. I think this is because I'm getting a value from an AutoNumber field and trying to assign the value to a number field. I'm at a loss.

I just tried an update using the value of DMax on a standard number field to update a number filed and Access still threw out a type conversion error. Why god, why?

 

[penguino29]

Hi exceii,

Keep at it - don't give up. I think you are almost there.

Just to check, can you type this in the VBA immediate window

debug.print DMax ("InvoiceID", "Invoices")

That should give you the maximum Invoice ID and all is well.

I just re-read your post and realised that you should also discard the "OrdersToInvoice" query because that intermediate query is surplus to requirement.

In your final update query, you simply select the criteria (in you case being InvoiceID=1) and update to DMax ("InvoiceID", "Invoices") at the same time! Cool.

What I mean graphically is to design the following new update query and try it :

Let us know if it works. Good luck.

.

 

[exceii]

Hi exceii,

Let us know if it works. Good luck.

.

I just tried your sugestion and it worked! Thank you.

 

[penguino29]

Thanks. I am glad it worked out and sorry for giving the correct solution a bit late.

Do come back here if you have more questions. Good luck with your database project!

 

[exceii]

Thanks. I am glad it worked out and sorry for giving the correct solution a bit late.

Do come back here if you have more questions. Good luck with your database project!

Hey Penguino,

Another problem has inevitably arose. I cannot seem to be able to create and save a new record using a macro embedded on a button control in a form I am using to display the records to be assigned to an invoice.

Basically I need to create a new Invoices record to be able to assign the DMax AutoNumber value of InvoiceID to the Records InvoiceID field.

I first use the OpenTable action on Invoices (because Access complained about the object being worked on not being open). Then, for some reason, when using the GoToRecord action passing New to the parameter, a new record is not made. I even included the RunMenuCommand SaveRecord straight after it but that didn't help ether. I suspect this is because the GoToRecord action is meant to be used on forms instead of tables.

As I write this I've just had an idea to make a form that inputs records into the Invoices table. I'll get back to you on if this works.

Thanks for all your help.

 

[penguino29]

Hi exceii,


I am not so hot with macros (have been converted to VBA ), but my guess would be to use the "After Insert" event property of the form, and then use SetValue in a macro to assign the Invoice DMax value to the InvoiceID box.

 

[exceii]

I ended up running an append query to the Invoices table to create a new InvoiceID before running the UpdateOrdersInvoiceID query.

I imagine VBA is a lot more powerful and flexible so hopefully I'll be able to learn some of it enough to be able to to use it in the future.

Thanks for your help.

 

[penguino29]

Yes - good thinking! As long as it does the job safely then its a solution!

You are welcome and hope to see you around again soon.