Weird problem with Email field

[RichO]

Hey everyone,

In my form I have a button that copies the information from one record to another and opens to the newly created record. This is a sample of the code beneath the button:

    Dim rs      As Recordset
    Dim db      As Database
    
    DoCmd.RunCommand acCmdSaveRecord
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM tb_Jobs WHERE Job_Number = " & Me.Job_Number)
    
    DoCmd.GoToRecord , , acNewRec
    
    Me.Customer = rs!Customer
    Me.Customer_Address = rs!Customer_Address
    Me.Customer_City = rs!Customer_City
    Me.Customer_State = rs!Customer_State
    Me.Customer_Zip = rs!Customer_Zip
    Me.Home_Phone = rs!Home_Phone
    Me.Fax = rs!Fax
    Me.Email = rs!Email

This code works fine EXCEPT that it won't copy the Email field. I have tried wrapping the field name in [brackets] but that doesn't make any difference.

There is data in the Email field but Access returns a null value for rs!Email.

Can anybody explain why it is treating this field differently? I've tried everything and I can't figure this one out

Thanks

 

[gemma-the-husky]

put a breakpoint by this block of code

step through it

check that there really is a value for rs!email

 

[RichO]

On the form, the field contains a value along with the other fields in the code.

However, upon executing the code, rs!Email is null and that's the problem. All of the other fields in the recordset contain the current values from the form fields.

 

[vbaInet]

Try changing the name of your field to something else. And also try casting it to a string too (even though it's a string). Cstr() os StrConv()

 

[RichO]

Thanks for your replies. Finally got it figured out but it had nothing to do with the actual field or field name.

Somehow the primary key (Job Number) properties got changed and there was a duplicate record in the table that had all of the information in the record I was working on except for the e-mail address and it was copying the information from that record instead of the one that I was looking at on screen.

 

[georgedwilkinson]

Not related to the answer to your question but it could impact it. Not knocking you, I'm just trying to be helpful.

It appears your design does not make use of normalization. I may be wrong, but you really should never have to do what you're doing. If you look up normalization, you'll find that many of the definitions include the term "data anomalies". Normalization is to remove data anomalies. You appear to have many.

That said, it could explain several things:
1. Why you even needed to write VBA code in the first place. Microsoft spent millions of dollars making a product that does most of a coders work for them. With our limited budgets, I cannot imagine being able to come close to the functionality they can afford to provide.
2. Why you have to copy fields that should be mostly static.
3. Why you have duplicate keys.

I'm guessing you have other data anomalies that we cannot grok by reading this thread.

I realize it may be too late to do anything about it. Just trying to get the cause of your problems out there so maybe someone in the future can avoid the same thing.

 

[RichO]

The database I am working with is not used by myself but I design and maintain it for another person. I don't own the customer's data so I am just using a back end of sample data for testing purposes. The duplicate key error was only in my sample data and it was an oversight on my part when I recently wiped the sample data clean and recreated the main table using a make-table query. His tables and data are fine with proper keying no duplicates.

The reason for this code is that only select fields are copied over to a new record. There are about 30 fields in the table and only about half of them are copied over, the rest has to be hand entered. This table keeps track of an event calendar of sorts and none of the data in this table is static.

Thanks for your input though.

 

[georgedwilkinson]

The database I am working with is not used by myself but I design and maintain it for another person. I don't own the customer's data so I am just using a back end of sample data for testing purposes. The duplicate key error was only in my sample data and it was an oversight on my part when I recently wiped the sample data clean and recreated the main table using a make-table query. His tables and data are fine with proper keying no duplicates.

The reason for this code is that only select fields are copied over to a new record. There are about 30 fields in the table and only about half of them are copied over, the rest has to be hand entered. This table keeps track of an event calendar of sorts and none of the data in this table is static.

I figured there were probably reasons. The advice is still valid. A table with 30 fields seems to scream "not normalized"!

Thanks for your input though.

Thanks for taking it in the right spirit!