Search Edit & Update using the Userform

[Chrisb812]

I'm looking for some help with code

I'm new to userforms & vba coding, after some research and reading some of the threads on this forum, i managed to create a Userform with a variety of textbox's, combobox's, option selection, i have three command buttons "Enter Clear Close" all these are working fine.

My question is, i have created another command buttons to Search, to make edits to a record, i can't seem to get the code right, if i attach my file, can some have a look, please.

Thanks in advance
Chris

[nankw83]

Hi Chris & welcome to the forum,

Yes, if you post a sample file & indicate exactly what you need to achieve, people will provide you the help required ... Just follow the instructions at the top of the page on how to attach a file

[Chrisb812]

I've attached a slimmed-down version of what I'm trying to achieve, everything is working except the Search & Update command buttons

[nankw83]

Try below code ... You can add more fields

Private Sub CommandButton2_Click()
'update record

Dim Rg As Range
 
If VinNumber.Value = "" Then
   MsgBox "Please enter the VIN #", vbExclamation
   VinNumber.SetFocus
   Exit Sub
End If

With Sheets("Stock Table").ListObjects(1).DataBodyRange
   Set Rg = .Columns(1).Find(VinNumber.Value, lookat:=xlWhole)
   If Rg Is Nothing Then
      MsgBox VinNumber.Value & " is not available !", vbExclamation
      Exit Sub
   Else
      Rg.Offset(, 2).Value = VehicleMake.Value
      Rg.Offset(, 4).Value = StockLocation.Value
      Rg.Offset(, 5).Value = GefcoReference.Value
      Rg.Offset(, 12).Value = PodReference.Value
   End If
End With

End Sub

Private Sub CommandButton3_Click()
'search button
 
Dim Rg As Range
 
If VinNumber.Value = "" Then
   MsgBox "Please enter the VIN #", vbExclamation
   VinNumber.SetFocus
   Exit Sub
End If

With Sheets("Stock Table").ListObjects(1).DataBodyRange
   Set Rg = .Columns(1).Find(VinNumber.Value, lookat:=xlWhole)
   If Rg Is Nothing Then
      MsgBox VinNumber.Value & " is not available !", vbExclamation
      Exit Sub
   Else
      VehicleMake.Value = Rg.Offset(, 2).Value
      StockLocation.Value = Rg.Offset(, 4).Value
      GefcoReference.Value = Rg.Offset(, 5).Value
      PodReference.Value = Rg.Offset(, 12).Value
   End If
End With

End Sub

[Chrisb812]

Hi nankw83

Thank you for your code, i am still getting an error? Please see screenshot

[nankw83]

Hi nankw83

Thank you for your code, i am still getting an error? Please see screenshot

You're getting this error message because there's no sheet called 'Stock Table' like your sample file ... I have attached the file I worked on for your reference

[Chrisb812]

You're getting this error message because there's no sheet called 'Stock Table' like your sample file ... I have attached the file I worked on for your reference

That's absolutely perfect, i can see it now, thank you so much for your help.

[Chrisb812]

Screenshot (1).png

Screenshot of message

[torachan]

@Chrisb812, alternative approach using table as data storage - all actions as you would need - ADD/SEARCH/UPDATE/REMOVE.
Note, rather than tedious fill all the labels, leave the defaults and loop through the table headers when initializing the form.
Also same principal with the textboxes (leave default names and with a single loop populate them and/or store updated info.
You will see some of the textboxes have a hidden combobox under them for choice from the validation lists.
any queries - happy to answer.
torachan.

[nankw83]

Glad to help

If that takes care of your request, please take a moment & mark this thread as [SOLVED] from the Thread Tools above your first post & consider * Add Reputation by clicking the star under the usernames of those who have help

[Chrisb812]

Thank you again for all your help, I have one last thing I added a delete button, can you help me with the code to delete the entire row for that record, with a message box with a Yes/No confirmation to delete

[nankw83]

You can utilize the same code ...

Private Sub CommandButton4_Click() '<--- ensure the button name is correct
'delete button
 
Dim Rg As Range
 
If VinNumber.Value = "" Then
   MsgBox "Please enter the VIN #", vbExclamation
   VinNumber.SetFocus
   Exit Sub
End If

With Sheets("Stock Table").ListObjects(1).DataBodyRange
   Set Rg = .Columns(1).Find(VinNumber.Value, lookat:=xlWhole)
   If Rg Is Nothing Then
      MsgBox VinNumber.Value & " is not available !", vbExclamation
      Exit Sub
   Else
      If MsgBox("Delete Row ?", vbYesNo + vbQuestion) = vbYes Then Rg.EntireRow.Delete
   End If
End With

End Sub

[Chrisb812]

That's perfect, thank you

I noticed a problem with the code you wrote for the search and update, when I do a search the data returned do not load in the correct fields on the form, and then when i update, the data doesn't update to the correct cells on the sheet. The Enter New macro is working, and sending the correct data to the correct cells, would mind having another look at the code.

Thanks in advance
Chris

[nankw83]

Hi Chris,

There's no issue with the code itself, you just need to change the mapping based on your cells ... I guess easiest way is just to copy from "Enter New" macro & do the required changes ...

    Rg.Offset(, 1) = VinNumber.Value
    Rg.Offset(, 2) = BARCODE.Value
    Rg.Offset(, 3) = VehicleMake.Value
    Rg.Offset(, 4) = VehicleType.Value
    Rg.Offset(, 5) = StockLocation.Value
    Rg.Offset(, 6) = GefcoReference.Value
    Rg.Offset(, 7) = DateIn.Value
    Rg.Offset(, 8) = DateOut.Value
    Rg.Offset(, 9) = ReceivingDealer.Value
    Rg.Offset(, 11) = PodDate.Value
    Rg.Offset(, 12) = PodReference.Value
    Rg.Offset(, 13) = Notes.Value

[Chrisb812]

I tried that, still didn't map to the right fields.
here a pic of the result and search mapScreenshot (6).png
.
.

[Chrisb812]

The maping

[nankw83]

Are all fields shifted by 1 column ? If yes, try to reduce the .offset(,x) by 1 ... The Rg is starting from the first column of your table not the first column of the sheet

[Chrisb812]

They seem to be -1, column 3 is in 2, 4 is in 3, .... and so on

[Chrisb812]

@nanwk83
That seems to have done the trick, column 1=0 and 2=1 and so ........ but all good now.

Thank you again
Chris

[nankw83]

Glad to help & thanks for reporting back