How to create an unique ID using another field

[Omeo]

Hi All



I am I would say intermediate level in access however I am trying to create a data base and this issue is just confounding me.

I am trying to create a form that has a drop down list for a number of brands. However under this i have another field called Brand iD. What I am trying to achieve is that each time you choose a brand from the drop down list, when you then go to the Brand ID drop down it will generate an unique ID that contains the Brand that was selected and today's date.

i have tried it via queries and nested iff formulas but i believe i am just over complicating it.

Any help would be greatly appreciated

Cheers

[June7]

Why do you need to create this ID? Just save the BrandID and the date into separate fields.

Why do you have two dropdowns for the brand info?

[Omeo]

I am creating a Data base to be used by the team. We have 5 different Brands and i wanted the form to have a drop down that allows them to choose the brand and then another field that generates a unique ID by combining the brand they selected with today's date?

[June7]

But why? Is the value used as a foreign key in another table?

If you just want to prevent duplicate pairs in the data entry, set these two fields as compound index in the table.

Can always concatenate the fields, which is easier than splitting text.

[orange]

What are you trying to do in plain English -no jargon and no quasi-code?
Tell us about the business this proposed database will support.

I recommend you work through some tutorials about database and table design.
Here is one I recommend.

I suggest you review the materials here also.

[Omeo]

Basically we receive order requests from various brands and this information needs to recorded in a data base. However for each order request I would like to give this a unique ID to help identify them.

I want to create a form that my team can simply fill in without having to access the data sheets. I have created the forms but I am having trouble creating a field that generates the unique ID based on the brand they chose and todays date.

[June7]

The records are uniquely identified by the two fields. There is no need to combine the values into another field. They can be concatenated for display as one on report.

Saving calculated data is usually a bad practice. However, if you must, will require code as you already know. The real trick is figuring out what event to put the code into. Perhaps the combobox AfterUpdate:

Me.ID = Me.Brand & Date()

[Omeo]

thanks for your help, i have now manged to create the form using the two fields i required and amending the control source. On my form i have created a command button that links back to my append query so it can update my main table with the information i require for the brand ID. However one of the fields fails to update. I have told the append query to amend the Brand ID in the master table by joining ID: [Brand] & Date() and amending this to the Brand ID in the main table. It actions this in the append query and I can view it in the append query data sheet but does not in the main table.

When i amend the ID with the string formula it takes away the option for me to amend this to the table. When I change the query and force it amend the main table i get a syntax error saying the formula is incorrect, however if i remove it from amending the table it accepts the formula?

Any ideas, sorry if this sounds confusing.

[June7]

What do you mean by 'append query' - why do you need this? APPEND (INSERT) sql action adds a new record. What is the code behind the button?

[Omeo]

My main data sheet contains brand and brand Id. The team only have access to the form. Now the form contains a brand field from a combo box and the brand ID field that gets generated by combining brand with the date which I set on the form in control source. I created a command button linked to append query that appends and replaces the brand id in the main data sheet by replacing it with the brand and date. Now this works when you view the append query but it will not populate in the main table?

[June7]

Is the form bound to the table you want to save the concatentated brand/date value into? If so, there is no need for the button and append query. Also, an APPEND query will not replace anything. I already provided code for saving the value to field of the form's RecordSource. You still haven't shown the code you are trying.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Omeo]

Thanks June7, I don't know why I was over complicating it, but I amended the events procedure after update using the Me.ID = Me.Brand & Date() you provided and it works like a charm. Both form and main table are updated.

Thanks for your help and patience