How update field in an unbound table

I have a form with a text box and a checkbox.  The form is unbound.  The textbox is named "txtJobN" and the checkbox is named "chkbxUrgent".  The user first enters a number in the "txtJobN" field.  Then they either check or uncheck the checkbox.  When they check or uncheck the checkbox I want the onclick event of the checkbox to update a record in tblJob where the key field equals the value in "txtJobN".

How?
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Helen FeddemaCommented:
First, I would recommend changing txtJobN to a combo box, with a row source of valid Job numbers.  This will avoid frustration for the users.  Then run this code from the checkbox's AfterUpdate event (I am assuming that you want the update to occur only when the checkbox is checked).  Of course, you will need to add code to to the actual updating, as indidated in the code.
Private Sub chkUrgent_AfterUpdate()

   Dim strJobNo As String
   Dim rst As DAO.Recordset
   Dim strSearch As String
   
   If Me![chkUrgent].Value = True Then
      strJobNo = Nz(Me![txtJobNo].Value)
      
      If strJobNo <> "" Then
         Set rst = CurrentDb.OpenRecordset("tblJobs", dbOpenDynaset)
         strSearch = "[JobNo] = " & Chr(39) & strJobNo & Chr(39)
         rst.FindFirst strSearch
         If rst.NoMatch = False Then
            rst.Edit
            'Your update code here
            rst.Update
         End If
      End If
   End If
   
End Sub

Open in new window

0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
Helen FeddemaCommented:
For the updating code, you need a field name, and a value to assign to it.  You may need to add more controls to your form to get this information.
0
 
SteveL13Author Commented:
Ok.  I have changed the field to a combobox.  When the user selects a record from the list I still need to update that record's "Urgent" field as true or false depending on what the user did with the checkbox which is named "chkbxUrgent".  I am not sure how to accomplish this.
0
 
Nick67Commented:
You aren't sure about this part?

         If rst.NoMatch = False Then
            rst.Edit
            'Your update code here
            rst.Update
         End If

it would be
    rst!WhateverFieldYouWantChanged = Me.chkUrgent.Value

         If rst.NoMatch = False Then
            rst.Edit
            rst!WhateverFieldYouWantChanged = Me.chkUrgent.Value
            rst.Update
         End If
0
 
Helen FeddemaCommented:
If Nick's comment is correct, then you need to remove the portion of my original code that proceeds only if the checkbox has a True value.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.