Problem using macro to generate unique ID in primary key field

[LanguidAnomie]

Hi,



I am creating a relational database for my A-level ICT coursework (this is the first time I've used database software) and am encountering a bit of trouble when trying to generate a unique ID in the primary key field of one of my forms using a macro.

I wrote a macro that takes the first character of the 'Comments' field, the last three characters of the 'CustomerID' field and the number of days that have elapsed since 01/10/2011 and strings them together to make a unique ID and put it into the primary key field called 'JobID'. The problem is that the macro, which I've set to run 'after update' of the 'Comments' field, doesn't run automatically, but as soon as I click on the JobID field, the macro runs just fine. There's nothing I can see that's telling the macro to run when I click on the field.

The macro details are as follows:

Action
SetValue

Arguments
[Forms]![FrmJob]![JobID], Left([Forms]![FrmJob]![Comments],1) & Right([Forms]![FrmJob]![CustomerID],3) & DateDiff("d",#01/10/2011#,Date())

Any idea how I can fix this? I have other macros that generate unique IDs in other forms that work just fine.

Thanks.

[June7]

Why do those other forms work? What is their setup? Is anything different from this form? If the macro actually is referenced in the AfterUpdate event property of Comments field, it should work. Sounds like the macro is associated with Comments LostFocus or Exit event or an event of JobID (GotFocus, Click, Enter).

Would have to analyse project if you want to provide it. I only use VBA but I can usually figure out macros.

[LanguidAnomie]

Why do those other forms work? What is their setup? Is anything different from this form? If the macro actually is referenced in the AfterUpdate event property of Comments field, it should work. Sounds like the macro is associated with Comments LostFocus or Exit event or an event of JobID (GotFocus, Click, Enter).

Would have to analyse project if you want to provide it. I only use VBA but I can usually figure out macros.

I'd be happy to provide you with my project. I tried to attach it to this message but it exceeds the size limit. Is there some other way I can get it to you? PM it or something?

Thanks.

[June7]

Did you run Compact & Repair first? Zip it? Still too big? Can upload to fileshare site such as box.com and post link to the file.

[LanguidAnomie]

Did you run Compact & Repair first? Zip it? Still too big? Can upload to fileshare site such as box.com and post link to the file.

Archived the file as .zip.

[June7]

I should have said this in my first post but I was little slow nailing down what bothered me about the issue. I would never construct unique IDs this way. For one thing, they are dependent calculations. Not only is there a requirement for a comment but the comment can never be edited, at least not the first character. And what if customer changes contact number? Editing these data through the forms will trigger the macros. Record associations will be royally wrecked.

Why did you take this approach?

Would be better off using the autonumber type as unique IDs.

You should set the JobID textbox as Locked Yes and TabStop No. TabOrder is bypassing the CustomerID combobox.

I tried the macro in another control's AfterUpdate event and still the same issue. Something about that form. Maybe rebuild it. Creat new form and copy/paste the controls. See if behavior changes.

[LanguidAnomie]

I would never construct unique IDs this way. For one thing, they are dependent calculations. Not only is there a requirement for a comment but the comment can never be edited, at least not the first character. And what if customer changes contact number? Editing these data through the forms will trigger the macros. Record associations will be royally wrecked.

Why did you take this approach?

Would be better off using the autonumber type as unique IDs.

You should set the JobID textbox as Locked Yes and TabStop No. TabOrder is bypassing the CustomerID combobox.

I tried the macro in another control's AfterUpdate event and still the same issue. Something about that form. Maybe rebuild it. Creat new form and copy/paste the controls. See if behavior changes.

The only reason I did it like that was because the guide I was using told me to, but you've convinced me to change it to an autonumber.

Thanks for all the help. Really appreciate it.

[June7]

I am shocked that any reference would recommend such a schema. There could be a way to generate ID from those components and have it remain unchanged. The event would have to include conditional statement like If IsNull(ID) Then create ID. However, the id components are still dependent on other data input for their structure. If no comment or phone contact, id will not conform to the structure and if code was in AfterUpdate event, it wouldn't even get built. Still bad design. Glad you chose the good Force.

[LanguidAnomie]

I am shocked that any reference would recommend such a schema. There could be a way to generate ID from those components and have it remain unchanged. The event would have to include conditional statement like If IsNull(ID) Then create ID. However, the id components are still dependent on other data input for their structure. If no comment or phone contact, id will not conform to the structure and if code was in AfterUpdate event, it wouldn't even get built. Still bad design. Glad you chose the good Force.

I've come to a new problem now. If you'd be willing to take a look at it then I'd be eternally grateful.

Here's the link to it:

https://www.accessforums.net/showthr...330#post110330