how to fill same data ? 1

[budich]

I have one table (imported from another program), and the structure is not quite good for inputing data. Let me give example, the table is like below :

new_no invoice_no docket_no volume etc
1001 42 5
1001 43 7
1001 44 6
1002 45 4
1002 46 4
1003 50 5

My question is how to make an entry form for fill in a same number (in new_no) for same invoice_no. In other words, I want to give new_no 0001 for invoice_no 1001 without repeating entry "0001" three times. Thanks alot

 

[kathryn]

If I understand you correctly, what you need is an Update Query.

Is there a rule to the way you want to format your new numbers? Is it always "0" and then the last three digits? If so, you can do the change using one update query. If it is more complex, you may have to run a few update queries.

Open a new query, add your table, and from the menu choose Query -> Update Query. Add you new_no field to the query and in the Update To: row add the expression that you want it to update to. If your rule is the one I mentioned above, your Update To: would be

"0" & [invoice_no]

Hope that helps



Kathryn

 

[budich]

Hi Kathryn,

Thank's alot for your answer, but I am sorry if my question was not clear to you.
The problem is not how to write "0001" instead of "1", but I want to make a entry form based on the table, and I want to inputing the new_no field. If I press "next button", I have to fill 0001 (or any number could be 1001) 3 times (as my example), how to fill only 1 times and writes itself to me 3 times.
would you please give respon again, thank's a million

 

[kathryn]

OK, you want to input a number, new_no, one time and have it put in the table three times? Do you want anything entered in any of the other fields?

Do you want the three entries of new_no to show in the form immediately, or is it OK if the three entries just appear in the table?

Kathryn

 

[budich]

Hi Kathryn,
Ok, getting closed. I just want enter new_no , the other fields have been entered before. So in form like below :

New_no : blank
Invoice_no : 1001
Docket_no : 42
Volume : 5
Etc : etc

Top Prior Next Bottom

Actually I don’t like the structure of the table (but I get from import). If I have 2 table such as :
Table 1 Table 2
New_no Invoice_no
Invoice_no Docket_no
Volume
Etc
It would easier because I can make a form with subform, so when I enter new_no in main form it will be automattically link in table 2

Basically, my aim is enter numbers in new_no field with a form. I don’t care what it looks like in form as long as the three entries just appear in the table.

Budi

 

[kathryn]

I think I finally have it, but then again, maybe not.

In your first posting, if you enter XXXX in the new_no field for the record with invoice number 1001, you want it to fill in that new_no in every record that has invoice number 1001. Is that it?

I would agree that the two table system would work much better. Have you looked at the Tools -> Analyze -> Table option on the menu? it would create these two tables for you with very little work.

If you are working with an import, you should import into one temporary table, and then use queries to create or append to the two tables.

Back to your original question. The way to do this in a form is to run an Update query in the AfterUpdate event of the textbox into which you enter new_no.

The update query would basically say "for each record with a value of [Invoice_no], update [new_no] to me![new_no].

Let me know if this sounds like what you want.



Kathryn

 

[budich]

Great !, Kathryn .. You finally have it.

It is a good idea to split the original table into 2 tables, this is better than I have fill in new_no in every record that has invoice number 1001, is’n it!?

Is there any code or macro to do splitting table from a command ? (not from menu – tools – analyze …).

I will try your second idea with an afterupdate event in a form as well.

Thank you very much Kathryn, your idea is very good and helpful to me.

 

[kathryn]

Well, there isn't any code that does the splitting job for you in one command, but you could use two Make Table queries. The first would do a Select Distinct on the table and only take new_no and invoice_no. the second would Select all fields except new_no. You can execute the queries through code when you get new data from import. You could also make the queries Append queries, if you are adding new data with every import. The Make Table queries will overwrite any existing data.

Kathryn

 

[budich]

Thank's Kathryn,

Your help is useful, I will try both of your method and choose one the most applicable to my system.
see you!

 

[budich]

Hai Kathryn,

Are you still there ?
After try both of your idea, finally I choose the form idea. Because if I use make table queries is not suitable for my program.
When I run an update query in after update event, it was ok and can copy the same number to all data which have same invoice_no. The only problem is when we move to another record or close the form there is error message as below :

Write Conflict
--------------------------------------------
This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made.

Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.

-----Save record------Copy to clipboard -----Drop changes----------

I can press the button "save record" and everything is ok as my plan. But I want the message is not appear again and I will always do "save record".
Thank you very much for your help
regards,
Budi