CODEWITHSUNDEEP
vbams-access
Bryan Clinkenbeard
Bryan Clinkenbeard

Reputation: 23

Updating table with changes to textbox

I have a table of vehicle info (5 fields per vehicle) with the capabilities of multiple vehicles per person. I have all 5 fields and all vehicles going into a multidimensional array and populating a textbox based on the vehicle number (ie vic1, vic2...). I need to update the table if any of the vehicle information is change. I have a different text box for each vehicle. I'm not sure how to take the changes (additions/deletions/updates) and load it back into the table.

Private Sub cmdDone_Click()
  strMake1 = VicArray(0, 0)
  strModel1 = VicArray(0, 1)
  strYear1 = VicArray(0, 2)
  strColor1 = VicArray(0, 1)
  strPlate1 = VicArray(0, 1)

  strMake2 = VicArray(1, 0)
  strModel2 = VicArray(1, 1)
  strYear2 = VicArray(1, 2)
  strColor2 = VicArray(1, 3)
  strPlate2 = VicArray(1, 4)
.
.
.
  strMake6 = VicArray(5, 0)
  strModel6 = VicArray(5, 1)
  strYear6 = VicArray(5, 2)
  strColor6 = VicArray(5, 3)
  strPlate6 = VicArray(5, 4)

strUpdate = "Update VehicleInfo SET " & _
"Vic1License = '" & strPlate1 & "',Vic1Make = '" & strMake1 & "', Vic1Model = '" & strModel1 & "', Vic1Year = '" & strYear1 & "', Vic1Color = '" & strColor1 & " Handicapped= " & strhandicap & "'," & _
"Vic2License = '" & strPlate2 & "',Vic2Make = '" & strMake2 & "', Vic2Model = '" & strModel2 & "', Vic2Year = '" & strYear2 & "', Vic2Color = '" & strColor2 & "'," & _
"Vic3License = '" & strPlate3 & "',Vic3Make = '" & strMake3 & "', Vic3Model = '" & strModel3 & "', Vic3Year = '" & strYear3 & "', Vic3Color = '" & strColor3 & "'," & _
"Vic4License = '" & strPlate4 & "',Vic4Make = '" & strMake4 & "', Vic4Model = '" & strModel4 & "', Vic4Year = '" & strYear4 & "', Vic4Color = '" & strColor4 & "'," & _
"Vic5License = '" & strPlate5 & "',Vic5Make = '" & strMake5 & "', Vic5Model = '" & strModel5 & "', Vic5Year = '" & strYear5 & "', Vic5Color = '" & strColor5 & "'," & _
"Vic6License = '" & strPlate6 & "',Vic6Make = '" & strMake6 & "', Vic6Model = '" & strModel6 & "', Vic6Year = '" & strYear6 & "', Vic6Color = '" & strColor6 & "'," & _
" WHERE ( LastName= '" & TxtLast & "', AND FirstName= '" & txtFirst & "')"

DoCmd.RunSQL strUpdate

Upvotes: 0

Views: 629

Answers (2)

Bryan Clinkenbeard
Bryan Clinkenbeard

Reputation: 23

I actually split it by a delimiting variable and loaded it into a different array.

SplitArray1 = Split(txtVic1.Value, ",")
strMake1 = SplitArray1(0)
strModel1 = SplitArray1(1)
strYear1 = SplitArray1(2)
strColor1 = SplitArray1(3)
strPlate1 = SplitArray1(4)
.
.
.

Upvotes: 0

Eedz
Eedz

Reputation: 116

I think you are trying to do manually what Access can do for you automatically.

I would suggest setting the recordsource of your form to the table VehicleInfo. Then, set the control source of each control to the fields of that table.

Try this article, it should give you a good intro to using Access more effectively.

https://support.office.com/en-us/article/introduction-to-forms-e8d47343-c937-44e8-a80f-b6a83a1fa3ae

I'm thinking you should have your VehicleInfo form filtered for the name chosen from the first form. The first form could have something like this:

public sub btn_Click()
    DoCmd.OpenForm "VehicleInfoForm", , , "Name = '" & selectedName & "'"
end sub

And then the vehicle info form would display the information for that user. Whenever they make changes to the text boxes, it would be saved when they close the form. Assuming it is all bound properly.

Upvotes: 1

Related Questions