Solved How to update fields in Table from values of text boxes in Form? (1 Viewer)

bnefit

New member
Local time
Today, 20:17
Joined
Jul 28, 2020
Messages
23
Hi All

I am working towards getting my database going and I have encountered another problem. Will be greatly appreciated if someone can provide some help. :)

Based on the selection of the combo boxes (cboLoc and cboItem), it will look up the Current Test Date and Current Test Tag values from tblTestTag. These values will then be displayed in txtCurrentDate and txtCurrentTag.

1596008686968.png


txtNewDate and txtNewTag are manually entered after inspection. I want both txtNewDate and txtNewTag to be entered before cmdSave is enabled and their values to be updated and saved in tblTestTag after clicking cmdSave.

I have attempted to write the SQL as follows but it did not work because of my limited VBA knowledge.

Code:
Private Sub cmdSave_Click()

Dim db As Database

CurrentDb.Execute

If Not IsNull(Me.txtNewDate) Or Me.txtNewDate = "" Then
If Not IsNull(Me.txtNewTag) Or Me.txtNewTag = "" Then

Update tblTestTag
Set TestDate = Me.txtNewDate.Value And TagNo = Me.txtNewTag.Value
WHERE Location = cboLoc And Item = cboItem

End Sub
Thank you in advance.
 
Last edited by a moderator:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:17
Joined
May 7, 2009
Messages
19,169
Code:
Private Sub cmdSave_Click()

If IsNull(Me.txtNewDate) = False And IsNull(Me.txtNewTag) =  False Then

Currentdb.Execute "Update tblTestTag " & _
"Set TestDate = #" & Format(Me.txtNewDate,"mm\/dd\/yyyy") & "# ,TagNo = " & Me.txtNewTag & " " & _
"WHERE Location = " & cboLoc & " And Item = " & cboItem & ";"

End If
End Sub
 

bnefit

New member
Local time
Today, 20:17
Joined
Jul 28, 2020
Messages
23
Hi arnelgp, thanks for your help.

I ran the code but it is giving me the error message below. I have been trying to see if I can identify the missing operator but was unsuccessful. Any suggestions?

1596063622093.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:17
Joined
May 7, 2009
Messages
19,169
are the rest of the fields Text, then change to:
Code:
Private Sub cmdSave_Click()

If IsNull(Me.txtNewDate) = False And IsNull(Me.txtNewTag) =  False Then

Currentdb.Execute "Update tblTestTag " & _
"Set TestDate = #" & Format(Me.txtNewDate,"mm\/dd\/yyyy") & "# ,TagNo = '" & Me.txtNewTag & "' " & _
"WHERE Location = '" & cboLoc & "' And Item = '" & cboItem & "';"

End If
End Sub
 

bnefit

New member
Local time
Today, 20:17
Joined
Jul 28, 2020
Messages
23
Hi arnelgp

Thanks for your help once again and it is working now. I had to change the code slightly to make it work.

Code:
Private Sub cmdSave_Click()

If IsNull(Me.txtNewDate) = False And IsNull(Me.txtNewTag) =  False Then
Currentdb.Execute "Update tblTestTag " & _
"Set TestDate = #" & Format(Me.txtNewDate,"mm\/dd\/yyyy") & "# ,TagNo = " & Me.txtNewTag & " " & _
"WHERE Location = '" & cboLoc & "' And Item = '" & cboItem & "';"

End If
End Sub

I am trying to update a 3rd field but it is showing me this error. Is it because 2 conditions are not enough to update 3 fields?

1596069409880.png


The code that I now have is:

Code:
Private Sub cmdSave_Click()

If IsNull(Me.txtNewDate) = False And IsNull(Me.txtNewTag) =  False And IsNull(Me.txtNewNotes) =  False Then

Currentdb.Execute "Update tblTestTag " & _
"Set TestDate = #" & Format(Me.txtNewDate,"mm\/dd\/yyyy") & "# ,TagNo = " & Me.txtNewTag & " ,Notes = " & Me.txtNewNotes & " " & _
"WHERE Location = '" & cboLoc & "' And Item = '" & cboItem & "';"
MsgBox ("Record Updated")

Else
MsgBox ("Please enter New Test Date, New Tag Number")

End If
End Sub
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:17
Joined
May 7, 2009
Messages
19,169
open the table and see if all the fields in the SQL can be found in the table, otherwise modify the sql string to the correct fieldnames.
 

bnefit

New member
Local time
Today, 20:17
Joined
Jul 28, 2020
Messages
23
All the fields in the SQL can be found in tblTestTag....

1596070311768.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:17
Joined
May 7, 2009
Messages
19,169
based on the pics you showed, TestDate is a Text, change it to Date/Time.
this will eliminate your headache when you want to involve testdate to compute dates, eg
when was the last testdate, how many was tested on that date, etc.

in fact, all of your fields a text.
 

bnefit

New member
Local time
Today, 20:17
Joined
Jul 28, 2020
Messages
23
Hi arnelgp

TestDate has always been set as Date/Time in all of my Tables and Queries.
txtCurrentDate and txtNewDate in frmTestTagUpdate are also set as "Short Date" format in Properties.

As you suggested, all the fields in the SQL can be found in tblTestTag but the error still exist.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:17
Joined
May 7, 2009
Messages
19,169
you post tblTestTag with at least a record.
 

bnefit

New member
Local time
Today, 20:17
Joined
Jul 28, 2020
Messages
23
tblTestTag (just a screenshot of the entire Table. There are many more records in the table.)
1596072921510.png


After inspection of any Item, I would like to update tblTestTag with the new TagNo, TestDate and Notes. In this instance, I want to inspect Troubleshoot Light from HIGHVOLT RTS.

Using frmTestTagUpdate,
Based on the selection of the combo boxes (cboLoc and cboItem), it will look up Current Test Date, Current Test Tag and Notes values from tblTestTag. These values will then be displayed in txtCurrentDate, txtCurrentTag and txtCurrentNotes.

txtNewDate, txtNewTag and txtNewNotes will be manually entered after inspection and to be updated in tblTestTag after clicking cmdSave.
1596073138516.png


My initial post of updating txtNewDate and txtNewTag into tblTestTag worked with your help. However, I now wanted to update txtNewNotes as well but the modified coding is giving me an error.
Code:
Private Sub cmdSave_Click()

If IsNull(Me.txtNewDate) = False And IsNull(Me.txtNewTag) =  False And IsNull(Me.txtNewNotes) =  False Then

Currentdb.Execute "Update tblTestTag " & _
"Set TestDate = #" & Format(Me.txtNewDate,"mm\/dd\/yyyy") & "# ,TagNo = " & Me.txtNewTag & " ,Notes = " & Me.txtNewNotes & " " & _
"WHERE Location = '" & cboLoc & "' And Item = '" & cboItem & "';"
MsgBox ("Record Updated")

Else
MsgBox ("Please enter New Test Date, New Tag Number")

End If
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:17
Joined
May 7, 2009
Messages
19,169
just remember, if you are dealing with Text in SQL, use ' in the query.


Me.txtNewTag & " ,Notes = '" & Me.txtNewNotes & "' " & _
...
 

bnefit

New member
Local time
Today, 20:17
Joined
Jul 28, 2020
Messages
23
Thanks for the tip! It's working now!!! :D

Code:
Private Sub cmdSave_Click()

If IsNull(Me.txtNewDate) = False And IsNull(Me.txtNewTag) =  False And IsNull(Me.txtNewNotes) =  False Then

Currentdb.Execute "Update tblTestTag " & _
"Set TestDate = #" & Format(Me.txtNewDate,"mm\/dd\/yyyy") & "# ,TagNo = " & Me.txtNewTag & " ,Notes = '" & Me.txtNewNotes & "' " & _
"WHERE Location = '" & cboLoc & "' And Item = '" & cboItem & "';"
MsgBox ("Record Updated")

Else
MsgBox ("Please enter New Test Date, New Tag Number")

End If
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:17
Joined
May 7, 2009
Messages
19,169
(y)you're the man!
 

bnefit

New member
Local time
Today, 20:17
Joined
Jul 28, 2020
Messages
23
(y)you're the man!
No...YOU ARE!!! ;)

I hope you don't mind me asking a few more questions as I am trying to improve my Access skills. I want Notes to be updated from a Combo Box selection instead of text so I have tried the following codes but it is not updating. There's no error when I run it, just not updating.

Code 1

Me.txtNewTag & " ,Notes = '" & Me.cboNotes & "' " & _
...

Code 2

Me.txtNewTag & " ,Notes = '" & cboNotes & "' " & _
...

Code 3

Me.txtNewTag & " ,Notes = " & cboNotes & " " & _
...

Are you able to enlighten me here?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:17
Joined
May 7, 2009
Messages
19,169
i'm not good in teaching, only good on eating pizzas.
you can however, Google anything about ms access combobox/listbox.
 

bnefit

New member
Local time
Today, 20:17
Joined
Jul 28, 2020
Messages
23
I did Google and tried all the examples that I found but it is still not working...

Me.txtNewTag & " ,Notes = '" & Me.cboNotes.Values & "' " & _
Me.txtNewTag & " ,Notes = '" & Me.cboNotes.Text & "' " & _
Me.txtNewTag & " ,Notes = '" & Me.cboNotes.Column(1) & "' " & _

But none of these worked. Anyway, I'll keep looking and trying. If I still can't get it to work, I'll post the question in a new thread as I think it is a different question to the title of this thread. I should mark this thread as "Solved".
 

bnefit

New member
Local time
Today, 20:17
Joined
Jul 28, 2020
Messages
23
I've managed to get the coding to work. Thanks for all the help. :)
 

Users who are viewing this thread

Top Bottom