Modifying Table field after update

Wd2048 · August 21st, 2009, 11:30 AM

Hi All,

I'm not a very adept VBA coder, so I am humbly asking for assistance.

I have a table with the following fields:
ID
LastName
FirstName
LastFirst

I have a form to add new contacts to the list. When a record is updated with First and Last Name, I would like to have the database update LastFirst with 'LastName' & ", " & 'FirstName'

I put an event on After Update for

Code:

LastFirst = LastName & ", " & FirstName

and the first record worked fine. Since that first record, I cannot advance to a new record, and when I try to close the form, I get an error that the database cannot be saved, so obviously I am not doing something right. I 'THINK' I need to reference the record that I am on (ID maybe) in my VBA, but I'm not sure how to do that or even if that is what needs to be done.

I use the LastFirst field for 2 purposes:
1. I populate a combo box on a different form with this field.
2. I use this to link the two tables together for reporting.

Any suggestions would be greatly appreciated!
-Wd

gbianchi · August 21st, 2009, 11:56 AM

My answer would not be fully helpful for you, but using a volatile field to link tables is not a good idea, and also to fill a combo you can build that field on the fly using SQL.

Wd2048 · August 21st, 2009, 11:59 AM

Quote:

Originally Posted by gbianchi

My answer would not be fully helpful for you, but using a volatile field to link tables is not a good idea, and also to fill a combo you can build that field on the fly using SQL.

Ok, I would be willing do to that. Don't really care how the data gets there as long as it is there. How would I go about that?

gbianchi · August 21st, 2009, 01:47 PM

To populate the combo just modify your query to get lastname, firstname.. something like
SELECT lastname & ', ' & firstname AS fullname FROM table...

Wd2048 · August 21st, 2009, 02:07 PM

I think I am on my way! Thanks so much!