Field Not Updating - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Field Not Updating

Nilsen Septon

12 months ago

I have a "Project" form (ProjectF) where the Project ID (client is calling this the Project ID...not to be confused with the auto number 'ProjID' field which is the primary key....) - where the Project ID is really a date format (yyyy mm dd). That's not a problem. However, if they enter multiple projects on the same day, they want to identify those multiple projects as "A", "B", "C", and so forth.

My table has ProjID (autonum - primary key), ProjectID (date/time Defalut Value = Now()), ProjABC (short text), ProjNumber (short text).

My form (ProjectF) is currently displaying ProjectID (format = yyyy mm dd), ProjABC (Defalut Value = "A", ProjNumber (Default Value = =Format([ProjectID],"yyyy mm dd") & " " & [ProjABC])

When adding a new project, all fields populate as expected. However, when I change the ProjABC field to "B", the ProjNumber does not update. I've added a Me.Refresh to the ProjABC field as a 'After Update' event - that didn't work. - what am I missing.

	John Davy @Reply 12 months ago
Hi Nilsen I think you need something like this in the afterupdate of theProjABC. I added a dash with spaces but you can take it out. Private Sub ProjABC_AfterUpdate() Me.ProjNumber = ProjectID & " - " & ProjABC End Sub HTH John You might want this in the after update of the ProjectID

	Sami Shamma @Reply 12 months ago
Are they doing any math or date operations on the field ProjectID? If not, define it as short text and build its value accordingly.

Nilsen Septon OP @Reply

11 months ago

@ John Davy - I wasn't aware of being able to put a formula like that in the event - that worked. However, after working with the change and seeing it work the way I expected it to and closed up shop for the day, this morning something was broken.....not sure what it was as I worked on several things yesterday. Since I'm working in a copy, I decided to start over from where I was before the change was made and will walk through the process again.

@ Sami Shamma - no math or date operations - they just want a unique number automatically populated where the ProjectID reflects the date. Defining as a date field seemed to be the easiest as I'm not sure how to build that value when defined as short text (but would love to learn how as I can see that being handy). Is it done using 'casting' (have done that before a few times in SQL but not enough to get a handle on it).

Thanks again for the help!!

	John Davy @Reply 11 months ago
Hi Nilsen, It might be wise to do a compact and repair and also check to make sure your new code is intact. John

	Nilsen Septon OP @Reply 11 months ago
That's actually the first thing I did (compact/repair) and then closed and re-opened my db - same error. Re-working it in a fresh copy isn't bad as I was trying a few other things as well and I figured it might be easier to solidify this portion of the code and then move on to the next item on my list.

	John Davy @Reply 11 months ago
That is a wise move. John

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View