Inputing data from one text box to another.

[MunroeM]

Hello I have a form where the use will enter a new expiration date and fill in their name for tracking. I currently have them entering the new expiration date and name in two blank unbound text boxes. I attacked an image to help show what im trying to do.



The "New Expiration Date" and "Updated By" sections in the Green box are the unbound text boxes these are where the user will enter their New expiration date and fill in their name.

The Fields in the Red box are from the table "CalItemT". The Red Box is for Info only it can not be changed or even selected on this form. The Info Displayed here is dependent on the Serial Number Selected in the Blue Box.

The Blue Box is the Serial Number of the Cal Item being updated. Its the Primary key field for the "CalItemT" table.

When they click the Save and Exit button I want the "Previous Expiration" and Previous Updated By" data in the Red Box to be replaced with the "New Expiration Date" and "Updated By" Data in the Green box.

I added Text to the Image to show Field names or text Box Names and what table they link to.

I only have the Updated by section in the code right now as i figure i can just copy and paste and change a few names to get it to work for the new expiration date once i get it working.

I tried using this code to do it but i get a 3144 Syntax error

Dim SQL As String
SQL = "UPDATE CalItemT" & _
"SET [CalItemT].[UpdatedBy] =  [Me.UpdateBy]" & _
"WHERE SerialNum = Me.ComboboxSerNum "
DoCmd.RunSQL SQL

any help will be great.

Even if you know of a different method for doing what i am trying to do suggest it and i will see if it will work for me

Thanks

[pbaldy]

Not sure I follow, but the form value needs to be concatenated into the string. Presuming it's numeric:

SQL = "UPDATE CalItemT " & _
      "SET [CalItemT].[UpdatedBy] =  [Me.UpdateBy] " & _
      "WHERE SerialNum = " & Me.ComboboxSerNum

Note I also added spaces which may have caused a problem.

[ssanfu]

It appears you are not keeping a history of cal dates since you are using "UPDATE" in the SQL, so try this (on a copy of the dB).

Using Property Sheet/Other Tab/ Name property

In the blue box:
(ub = unbound)
Rename the unbound control "NewExpDate" to "ubNewExpDate"
Rename the unbound control "UpdatedBy" to "ubUpdatedBy"



In the red box:
(tb = text box)
Rename the bound control "ExpirationDate" to "tbExpirationDate"
Rename the bound control "UpdatedBy" to "tbUpdatedBy"



Then, code for the button:

    Me.tbExpirationDate = Me.ubNewExpDate
    Me.tbUpdatedBy = Me.ubUpdatedBy
    Me.Dirty = False

    Me.ubNewExpDate = Empty
    Me.ubUpdatedBy = Empty

[MunroeM]

It appears you are not keeping a history of cal dates since you are using "UPDATE" in the SQL, so try this (on a copy of the dB).

Using Property Sheet/Other Tab/ Name property

In the blue box:
(ub = unbound)
Rename the unbound control "NewExpDate" to "ubNewExpDate"
Rename the unbound control "UpdatedBy" to "ubUpdatedBy"



In the red box:
(tb = text box)
Rename the bound control "ExpirationDate" to "tbExpirationDate"
Rename the bound control "UpdatedBy" to "tbUpdatedBy"



Then, code for the button:

    Me.tbExpirationDate = Me.ubNewExpDate
    Me.tbUpdatedBy = Me.ubUpdatedBy
    Me.Dirty = False

    Me.ubNewExpDate = Empty
    Me.ubUpdatedBy = Empty

Perfect thanks the code made me realise what i was doing wrong befor when i was trying the same method you used. I was doing Me."name".Value = Me."othername"

now it works like a charm

[ssanfu]

Wonderful!!!