Select Query

[mdctsg]

Hello

I have a database in which two of my columns are ID (Populated) and Invoice (Empty) I have a query to populate the Invoice table based on an export which fills the query with the ID and Invoice number. The query works but I get an "Circular reference caused by 'Query' error message my statement is

SELECT Query.Invoice INTO Table1 FROM Query WHERE Query.ID=Table1.ID

Basically I just want to populate the Invoice field in Table1 where the ID match from the query.

Thanks Bob

 

[scriverb]

Here is an UPDATE query that will do what you want. Where the ID fields from both the table and the query match the query will update the Table1 Invoice field with the value in the Invoice field from Query.

UPDATE Table1 INNER JOIN Query on Table1.ID = Query.ID
Set Table1.Invoice = Query.Invoice;

Let me know if you have any problems. Bob Scriver

 

[mdctsg]

It's working but I am getting the same error message

Bob

 

[scriverb]

Try changing the name of your query from Query to anything else. Then make the changes in the code. Query is not a good name to give anything as it may be a reserved word with ACCESS. Give that a try. Bob Scriver

 

[mdctsg]

Same thing. I renamed the query to Return and changed the name in the statement.

 

[scriverb]

What tables are you using in your Query. They wouldn't possibly be Table1 would they as that may create a circular reference. Bob Scriver

 

[mdctsg]

Yes it was Table1. I added a table2 and it runs error free. The reason I was using a query to table1 is I need to run and update each record automatically each time a record is written without having to manually run the query. Can this be done?

Thanks
Bob

 

[scriverb]

Well the SQL can be run as an executeable VBA statement when you write a new record. This would be done behind some Save button of such. Why don't you describe in words rather than code just what you process is so that I can advise you as to how to make this work for you. Bob Scriver

 

[mdctsg]

Thanks so much for taking the time to help me.

What I am going for is:

In the database there is table1 which has customer information with the ID to define them. At the end is a blank column for Invoice number. In another program an Invoice will be generated after a sale. The program can export the ID number and Invoice number out to a table. But it will only go to the next available blank space. So if customer 1000 gets an Invoice the number would go to the top of the column where customer 1 is. What I want to do is have the invoice number populate the invoice field in table1 based on the ID number. I want to match the invoice with the correct customer. So if customer 2 and 5 get an invoice number it will go to them.

Example
ID Invoice
1
2 10001
3
4
5 10002
6

So instead of manually running the query I'd like it to just go to the correct field. Adding table2 and the query you gave me does the trick but I have to run manually the query.

Thanks
Bob

 

[scriverb]

When you say In another program an Invoice will be generated after a sale. are you referring to another form within this database that is being run to create this new file? Are the sales being generated one at a time and each time a sale is generated you want the file to be updated? Is that what you are looking for? Bob Scriver

 

[mdctsg]

The program that creates the invoice is a standalone application. It throws out an export of what data you want. So I can send it back to access.

Sales are being generated one at a time. And yes I want to update each time it comes out.

So the software creates the invoice, exports the data to access db, table updates

 

[scriverb]

I don't want to criticize the design here because I still don't completely understand the operational situation and there may be some compelling reason to be doing it this way. But, . . . What happens if there are two invoices generated for a customer? Do you want the second one to update over the first one? Are there only going to be one for customer ID? Do you refresh the data table each day to start over with a fresh recordset of customer data?

You see we can have ACCESS check ever 2 minutes or so for records in this table that is going to be exported to the ACCESS database and we can update through code in a forms On Timer event procedure the data to the table fields that you want. Is there ever a chance that your Invoice program will export into ACCESS a new invoice before we have updated with the old one.

Without knowing a little more about the process it is hard to make the choices on how to do this. Please keep updating more specifics. Bob Scriver

 

[mdctsg]

Far as I know if it's a case of multiples the ID will recieve a -1, -2 and a new line in the db so forth. Most of the time it will be only one.

Unfortunatly I am sort of 3rd party in this. The software is what it is and so is the DB. I am just rying to find a way to marry the two so it doesn't have to be in a seperate db. This was really the only thing I could think of. I understand what you are saying about the design.