combining field to create values

[IanMilly]

hello,

I have a database which has a part of it that creates serial numbers etc.

A serial numbers is created from a combobox and 2 txt boxes.

The combobox gives a letter, the first text box creates a date and the third uses an autonumber.

The three boxes then have info extracted into an unbound text box in the following format.

=[letter] & Format([date],"mmyy") & Format([serialnumber],"00000000")
Example: DI 05/07/04 00000009 => DI070400000009


this all works great - the problem being i need to save this new value in a table. how can this be done?

- the only reason the value needs to be saved in a table is that the data is to be imported into a commercial program to make some labels.

Any help would be appreciated

Thanks

Ian

 

[The_Doc_Man]

Of course, to save it, the text box has to be BOUND or you have to run SQL UPDATE or APPEND queries. Assuming you don't really want to do that, you could simply define the field in the table where this is applicable, but make the other fields unbound. Then in the OnChange events for the three component text boxes, for each one recompute the value of the generated key and store it in the bound text box. When you click on the SAVE icon or whatever other method you prefer, the bound box gets saved; the unbound ones don't.

 

[IanMilly]

The trouble with that is that i am using autonumbers to ensure no number replication, the letters are to be chosen from a drop down list with is from a table and the date value is as default set to todays date, with the user able to change this as and when required.

The three textboxes are bound to fields in a table - the textbox containing the joined values of all three text boxes is not bound.

How would i run the update query to append a table to the have a field containing the joined values of the three field values from the textboxes?


Thanks

Ian

 

[IanMilly]

Ok - I have now got it to update a field with the combined values of all three fields. I am using an update query to store the values of all 3 fields to a new field.

I am using the following sql

UPDATE TblMakingSerialNumber SET TblMakingSerialNumber.BarcodeZusammen = [letter] & Format([date],"mmyy") & Format([serialnumber],"00000000");

This updates the field barcodeZusammen with the letter then date (mm/yy) and an autonumber.

I have the sql to run on closing the form (as the user is forced to do this in order to return to a switchboard).

The query will be ran and asks the user if they want to update the fields being changed by the query. Is there any way of changing this to just let the fields be updated with no user input to update the fields.

The query updates all the fields - is there anyway of making it just update changed fields?

Should I be using the INSERT INTO sql type instead? I can't quite get that code to work -syntax problem. If anyone could give me the correct syntax for this it would be much appreciated

Thanks

Ian

 

[Pat Hartman]

You shouldn't be running an update query at all. The query you posted is updating EVERY row in your table every time it runs. All you need is a single line of code. Since letter is the only thing they type, you can put the code in the AfterUpdate event of the letter code. If you enter other parts of the code, I would put the statement in the BeforeUpdate event of the form.

Me.BarcodeZusammen = [letter] & Format([date],"mmyy") & Format([serialnumber],"00000000");

If you really have a field named [date], I would suggest changing it immediately. You will run into a problem sooner or later when you use function names as the name of your own objects. In the case of this particular duplication, you may not even get an error, Access may just use Date() when you wanted it to use the value in your date field.

 

[IanMilly]

Thanks pat, got that working fine now - much better without the update query.

Thanks for the advice on date field