Combobox and Update query problem

L

Lol999

Registered User.
Local time
Yesterday, 20:10
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: 113
  • Database Problem.docx
    Database Problem.docx
    13.7 KB · Views: 88
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.
 
Could you explain how I could do that please? Brain a little foggy.
 
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
 
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.
 
Is it possible to attach the db here?
 
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.
 
You didn't answer whether the form is bound to Tbl_Vehicle.
 
Apologies for that, as it stands the form is bound to Tbl_Vehicle and the combobox and textbox are unbound.

Thanks, Lol
 
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
 
Lol999 said:
Apologies for that, as it stands the form is bound to Tbl_Vehicle and the combobox and textbox are unbound.
Click to expand...

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.
 
pbaldy said:
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.
Click to expand...
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
 
arnelgp said:
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
Click to expand...
Thank you for the great bit of code, I will certainly enjoy trying to understand it!

Many thanks, Lol
 
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?
 
You must log in or register to reply here.

Similar threads

C
Cant Update Record
Replies
19
Views
637
Pat Hartman
P
K
Solved Combobox in Subform not updating data
Replies
2
Views
301
kiaconchim
K
Y
New with a problem
Replies
4
Views
390
Pat Hartman
P
S
Update Data Based on plat number and Date
Replies
7
Views
403
sandy70
S
B
Dynamic update of combobox via AfterUpdate and Me.Requery
Replies
11
Views
715
BumpyRide
B

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom