423,851 Members | 2,680 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,851 IT Pros & Developers. It's quick & easy.

how to save the result of calculated control (string) in a database table

P: 97
Hi

If anyone helps me on this problem, I'd greatly appreciate it!

I have a field name, updatedName, in my database that is the same as originalName for a user to start. I also have two text fields in my form, newFirstName and newLastName, and a calculated control (newName) of a text box to concatenate newFirstName and newLastName with a space between.

The logic is....
If the newFirstName is not blank, then the calculated control, newName, will display the newFirstName and originalLastName, and if a new info in newLastName is entered, then ..., vice versa.

Once there's a new info entered in newName, it should be saved in the updatedName in my database table. Since the newName control is unbound, however, it cannot be saved in updatedName in the table. This is my problem, and can anyone help me on this? Thanks!
Sep 21 '07 #1
Share this Question
Share on Google+
10 Replies


FishVal
Expert 2.5K+
P: 2,653
Hi, loisk.

So, what prevents you from storing FirstName and LastName in separate table fields?
Sep 21 '07 #2

P: 97
Hi, loisk.

So, what prevents you from storing FirstName and LastName in separate table fields?
Hi, FishVal!

The calculated control (newName) on the form, that concatenates the two fields (newFirstName & newLastName) is unbound, so it cannot be saved in the field (updatedName) in my db table. My question is that if there's anyway to get around it.
Sep 21 '07 #3

FishVal
Expert 2.5K+
P: 2,653
Hi, FishVal!

The calculated control (newName) on the form, that concatenates the two fields (newFirstName & newLastName) is unbound, so it cannot be saved in the field (updatedName) in my db table. My question is that if there's anyway to get around it.
This is quite understandable. Why not to store FirstName and LastName in separate table fields?
Sep 21 '07 #4

Expert 100+
P: 296
I agree with FishVal...why would you want to store the concatenated name? It is better practice to store First Name and Last Name as seperate fields. When you run queries and reports, you can concatenate the name at that point. Look at this article for a bit of an explanation as to why this practice is better: Database Normalization
Sep 21 '07 #5

P: 97
Thanks for both of your replies!

My Access database is linked to MySQL table and I have no control to change the structure of the table. The updatedName has to be updated based on the newFirstName and newLastName on my form.

If no change occurs, then the updatedName should display the originalName. Otherwise it should reflect the changes in the updatedName field because it will be used by other control later.

In the control source property of the newName I entered the following:
Expand|Select|Wrap|Line Numbers
  1. =IIf([M_nfname]>"" And [M_nlname]>" ",[M_nfname] & " " & [M_nlname],IIf([M_nfname]>" ",[M_nfname] & " " & [M_lname],IIf([M_nlname]>" ",[M_fname] & " " & [M_nlname],[M_fname] & " " & [M_lname])))
(Please excuse for the different names than my post)
It works fine in my calculated unbound control on the form, but I need to save any changes occurred in "M_nfname" (mother's new first name) text control (and so on) to the updatedName field in the table.
Sep 21 '07 #6

FishVal
Expert 2.5K+
P: 2,653
Thanks for both of your replies!

My Access database is linked to MySQL table and I have no control to change the structure of the table. The updatedName has to be updated based on the newFirstName and newLastName on my form.

If no change occurs, then the updatedName should display the originalName. Otherwise it should reflect the changes in the updatedName field because it will be used by other control later.

In the control source property of the newName I entered the following:

=IIf([M_nfname]>"" And [M_nlname]>" ",[M_nfname] & " " & [M_nlname],IIf([M_nfname]>" ",[M_nfname] & " " & [M_lname],IIf([M_nlname]>" ",[M_fname] & " " & [M_nlname],[M_fname] & " " & [M_lname])))

(Please excuse for the different names than my post)
It works fine in my calculated unbound control on the form, but I need to save any changes occurred in "M_nfname" (mother's new first name) text control (and so on) to the updatedName field in the table.
Ok.

I have a minor experience with linked MySQL tables, but I guess you table (linked through ODBC I gues) may be updated via bound form control.
So
  • bind the [newName] TextBox to [updatedName] table field.
  • put the following sub to the form module
    Expand|Select|Wrap|Line Numbers
    1. Private Sub GetFullName()
    2.     With Me
    3.         .newName = Nz(.M_nfname, .M_fname) & " " & Nz(.M_nlname, .M_lname)
    4.     End With
    5. End Sub
    6.  
  • invoke the sub from [M_nfname] and [M_lname] AfterUpdate event.
Sep 21 '07 #7

P: 97
Thank you, FishVal!

I just got back to my office and did read your reply.
Do I add the sub to my form's code page OR to a new module?
(Sorry for asking it but I am not-much-experienced one)

By invoking, do you mean to add it to the AfterUpdate event in the same code page?
Sep 24 '07 #8

FishVal
Expert 2.5K+
P: 2,653
Thank you, FishVal!

I just got back to my office and did read your reply.
Do I add the sub to my form's code page OR to a new module?
(Sorry for asking it but I am not-much-experienced one)

By invoking, do you mean to add it to the AfterUpdate event in the same code page?
Add the sub to the same form module. Run it from [M_nfname] and [M_lname] AfterUpdate event handlers
Expand|Select|Wrap|Line Numbers
  1. Private Sub M_nfname_AfterUpdate()
  2.     GetFullName
  3. End Sub
  4.  
  5. Private Sub M_lname_AfterUpdate()
  6.     GetFullName
  7. End Sub
  8.  
Sep 24 '07 #9

P: 97
It works!

Thank you so much!
You saved my day!
Sep 24 '07 #10

FishVal
Expert 2.5K+
P: 2,653
You are welcome.

Kind regards,
Fish
Sep 24 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.