Combobox and Update query problem (1 Viewer)

Lol999

Registered User.
Local time
Today, 01:15
Joined
May 28, 2017
Messages
184
Hi folks, not been doing much for a while and then the phone rings and it's work moaning about a problem :)
Please see attached sheet as the site insists I am posting 58000 characters regardless of the browser I use!
 

Attachments

  • Data Changed.jpg
    Data Changed.jpg
    39.9 KB · Views: 81
  • Database Problem.docx
    13.7 KB · Views: 57

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:15
Joined
Aug 30, 2003
Messages
36,118
Typically you would either use code to manipulate data or a bound form (most often just a bound form). Sounds like you've got both. Why not just set the value of a control on the form to the desired value? If you have some good reason for this, you can probably requery the form or explicitly save the form data before running the update.
 

Lol999

Registered User.
Local time
Today, 01:15
Joined
May 28, 2017
Messages
184
Could you explain how I could do that please? Brain a little foggy.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:15
Joined
Aug 30, 2003
Messages
36,118
I'm assuming that the form is bound to Tbl_Vehicle? Presuming you have a textbox on the form simply:

Me.TextboxName = Me.Txt_NewReg
 

Lol999

Registered User.
Local time
Today, 01:15
Joined
May 28, 2017
Messages
184
Interesting. What I am trying to achieve though is the use of a combobox to allow the user to select the offending registration quickly and then enter the new reg in a textbox. A click of a button would change the combobox value to the textbox value. Would your method work that way, and how would I set the data source for the combobox?

Sorry for dullard brain, been concentrating on MS Project for the last 3 months.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:15
Joined
Aug 30, 2003
Messages
36,118
Is it possible to attach the db here?
 

Lol999

Registered User.
Local time
Today, 01:15
Joined
May 28, 2017
Messages
184
Not really it has work sensitive data in there. The code I am using is in the word document I uploaded and I can answer any questions on structure etc.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:15
Joined
Aug 30, 2003
Messages
36,118
You didn't answer whether the form is bound to Tbl_Vehicle.
 

Lol999

Registered User.
Local time
Today, 01:15
Joined
May 28, 2017
Messages
184
Apologies for that, as it stands the form is bound to Tbl_Vehicle and the combobox and textbox are unbound.

Thanks, Lol
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:15
Joined
May 7, 2009
Messages
19,169
Code:
Private Sub Command35_Click()
    Dim strSQL As String
    Dim strRowSource As String
    
    DoCmd.SetWarnings False
    
    ' Only update if there is selection in combobox
    If [Cbo_VehicleReg_Current].ListIndex <> -1 Then
        ' save the rowsource of combo
        strRowSource = Me.Cbo_VehicleReg_Current.RowSource
        strSQL = "UPDATE Tbl_Vehicle SET Vehicle_Reg=" & Chr(34) & Txt_Newreg & Chr(34) & _
            " WHERE Vehicle_Reg=" & Chr(34) & [Cbo_VehicleReg_Current].Value & Chr(34) & ";"
        
        ' remove rowsource
        Me.Cbo_VehicleReg_Current.RowSource = ""
        DoCmd.RunSQL strSQL
        ' reinstate the rowsource
        Me.Cbo_VehicleReg_Current.RowSource = strRowSource
        ' set the value of combo to textbox before deleting its content
        Me.Cbo_VehicleReg_Current = Me.Txt_Newreg
        Me.Txt_Newreg = Null
        Me.Txt_Newreg.Visible = False
    End If
End Sub
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:15
Joined
Aug 30, 2003
Messages
36,118
Apologies for that, as it stands the form is bound to Tbl_Vehicle and the combobox and textbox are unbound.

If the controls are unbound, what's the purpose of having the form bound? If you make it unbound, your code should work without error. The error comes when Access sees the data being edited by two different sources, in this case the bound form and the code.
 

Lol999

Registered User.
Local time
Today, 01:15
Joined
May 28, 2017
Messages
184
If the controls are unbound, what's the purpose of having the form bound? If you make it unbound, your code should work without error. The error comes when Access sees the data being edited by two different sources, in this case the bound form and the code.
And this is the problem, I need a bit of re-education I think. What I have been doing is relying on the combobox wizard to set the data source and what it does.
What I would like to learn is how to do it manually for future references so if you have a link or the patience to do a step by step that would be much appreciated.

Cheers, Lol
 

Lol999

Registered User.
Local time
Today, 01:15
Joined
May 28, 2017
Messages
184
Code:
Private Sub Command35_Click()
    Dim strSQL As String
    Dim strRowSource As String
    
    DoCmd.SetWarnings False
    
    ' Only update if there is selection in combobox
    If [Cbo_VehicleReg_Current].ListIndex <> -1 Then
        ' save the rowsource of combo
        strRowSource = Me.Cbo_VehicleReg_Current.RowSource
        strSQL = "UPDATE Tbl_Vehicle SET Vehicle_Reg=" & Chr(34) & Txt_Newreg & Chr(34) & _
            " WHERE Vehicle_Reg=" & Chr(34) & [Cbo_VehicleReg_Current].Value & Chr(34) & ";"
        
        ' remove rowsource
        Me.Cbo_VehicleReg_Current.RowSource = ""
        DoCmd.RunSQL strSQL
        ' reinstate the rowsource
        Me.Cbo_VehicleReg_Current.RowSource = strRowSource
        ' set the value of combo to textbox before deleting its content
        Me.Cbo_VehicleReg_Current = Me.Txt_Newreg
        Me.Txt_Newreg = Null
        Me.Txt_Newreg.Visible = False
    End If
End Sub
Thank you for the great bit of code, I will certainly enjoy trying to understand it!

Many thanks, Lol
 

Lol999

Registered User.
Local time
Today, 01:15
Joined
May 28, 2017
Messages
184
Well I got it sorted in the end. I had the form bound to the Tbl_Vehicle, the combobox was unbound and its source set to all the vehicle registrations in Tbl_Vehicle, and the text box bound to Vehicle_Reg in Tbl_Vehicle.
I had the following code behind a button:
Code:
Private Sub Command35_Click()
Me.Cbo_VehicleReg_Current = Me.Txt_NewReg
DoCmd.RunCommand acCmdSaveRecord
Me.Cbo_VehicleReg_Current.Requery
End Sub

It all works great but I'm struggling to understand how the text box displays whatever is selected in the combobox?
 

Users who are viewing this thread

Top Bottom