Editing Records Through VBA Form

[skinfreak]

I have designed a nice VBA form that seems to work well but the powers that be have changed the spec. Initially the data entry was done all at once, but now they want to do this in stages.

My form features 4 tabs, and this corresponds more or less to the four stages of data entry (this was not intentional). What I need to do is give the user the ability to check if the record has been started and if so, append it.

Essentially I need a query to check for a customer number that is in Column A, and display data from this row to make the decision of either creating a new query or editing the current one (ie. data from certain other cells on that row).

It more or less needs to behave like Access with possibly forward and back arrows, but unfortunately Access is out of the question (£$£$!).

Am I on a loser with this? Are record search arrows possible in VBA, or should I scrap it and tell them to use Access? Anyone's ideas with this would be treated like a God for evermore...I don't want to bin what I've done but at the end of the day I'm not going to waste more time in case they change what they want again!

 

[sweater_vests_rock]

sf.

it wouldn't be too hard to search a worksheet for a number, and then activate that row if found. something like the below code would work. i'm not sure what you mean, though, by forward and back arrows?

cheers. ben.

Sub Test()
    Dim strID As String
    
    strID = "123"
    Call FindID(strID)
    
End Sub
Private Sub FindID(MyID As String)

    Dim rngSearch As Range, c As Range
    
    Set rngSearch = Intersect(Columns("A:A"), ActiveSheet.UsedRange)
    
    Set c = rngSearch.Find(What:=MyID, LookIn:=xlValues, LookAt:=xlWhole)
    
    If c Is Nothing Then
        MsgBox "ID not found."
    Else
        c.EntireRow.Activate
    End If
       
'   Empty object variables
    Set rngSearch = Nothing
    Set c = Nothing
    
End Sub

 

[skinfreak]

In Access, you can search between records with forward and back navigation arrows. That's what I meant.

Regarding your code, if the row is activated, how do I load the current values into the user form? Is it the inverse of pulling the values into the spreadsheet upon save?

EG:

ActiveWorkbook.Sheets("Path2005").Activate

    Range("A1").Select

    Do

    If IsEmpty(ActiveCell) = False Then

        ActiveCell.Offset(1, 0).Select
    End If

    Loop Until IsEmpty(ActiveCell) = True

    ActiveCell.Value = txtSurName.Value
    
    ActiveCell.Offset(0, 1) = txtForeName.Value
    ActiveCell.Offset(0, 2) = txtDOB.Value
    ActiveCell.Offset(0, 3) = txtAddress1.Value
    ActiveCell.Offset(0, 4) = txtAddress2.Value

 

[sweater_vests_rock]

[snip]

Regarding your code, if the row is activated, how do I load the current values into the user form? Is it the inverse of pulling the values into the spreadsheet upon save?

[snip]

yes, exactly right. so if i have a textbox named txtExample, then i can "load" this from a cell with

txtExample.Value = ActiveCell.Value

if you are really interested in loading certain cell values into the form, rather than actually showing the user the row in the worksheet, then i would skip the activation step (for example only) and just reference your values from your found cell c:

txtExample.Value = c.Value
txtExample2.Value = c.Offset(0,1).Value

etc

ben.

 

[skinfreak]

Oh My God. This is rather cool. :D

 

[skinfreak]

Am I best off putting this in as a public module or directly in the form code? i am having trouble activating it....

 

[patrickmuldoon99]

You should put the code in the form initialize event (or activate), sometimes excel seems to fritter between calling the two if the form hasn't been explicitly unloaded.


alternatively you could put it in a module and call the procedure with the arguments in the form.

 

[skinfreak]

You cannot put subroutines in the initialize section can you?

Private Sub UserForm_Initialize()

Sub Test()
    Dim strID As String
   
    strID = "123"
    Call FindID(strID)
End Sub

Private Sub FindID(MyID As String)

    Dim rngSearch As Range, c As Range
   
    Set rngSearch = Intersect(Columns("C:C"), ActiveSheet.UsedRange)
   
    Set c = rngSearch.Find(What:=MyID, LookIn:=xlValues, LookAt:=xlWhole)
   
    If c Is Nothing Then
        MsgBox "ID not found."
    Else
        'c.EntireRow.Activate
        txtSurName = c.Offset(0, -2).Value
        txtForeName.Value = c.Offset(0, -1).Value
        txtID.Value = c.Value
End Sub

I get a compile error "Expected End Sub".

 

[sweater_vests_rock]

sf.

Am I best off putting this in as a public module or directly in the form code? i am having trouble activating it....

you should call it from your initiatize event:

Private Sub Userform_Initialize()
    Call Test
End Sub

and place Test and FindID in a standard module.

ben.

 

[skinfreak]

OK. I created a new module called search, and placed Call Test in the initialize. When I run the form i get the "ID Not Found" message.

Now, the code that has been provided doesn't include a prompt does it? So I need to include a prompt to fill the StrID string in the initialise statment too, don't I?