Populate USerform based on search query

[pan_kaj]

I have a userform that I use to enter fault calls and it saves in to a spreadsheet.

I want to develop the capability to be able to have a form that Will search a pre-existing call and then when it finds it to bring up the main userform and populate it with existing details so that the user can amend them if reqd and save back to the database.

So what i want.

Userform - New Call
Userform - search existing call

I want to open 'search existing call' userform, be able to search based on one of the following: callrefnum, fault_type, Call_logged_by
Once the call is found - it should be displayed and then on double click it shd open up in the main userform 'New Call' with all existing details populated.

Any help or reference to a thread which answers this will be appreciated.

Thanks!
PJ

 

[pan_kaj]

Am not sure i fully understand but let me give it a try and then will ask if I need further help.

But many thanks for your help Alan!

Cheers!

 

[al_b_cnu]

Hi Pj,

sorry, didnt explain myself very well.


If your worksheet contains the headings 'H1, H2, H3, H4 and H5 in cells A1:E1 AND
your userform contains the following controls:
Label1, Textbox1
Lablel3,TextBox3
Label5, Textbox5

the code will output the headings 'H1', 'H3' and 'H5' into the labels and the corresponding data from the currently selected row.

 

[pan_kaj]

Hi,
I have tried this but it doesnt work. My headings in columns (spreadsheet called 'db') are as follows:

Name of Helpdesk Representative
Source
Mode
Fault Category
Fault Type
Service Vehicle Number
Head Code
call_ref_Num

Once the user clicks SAVE on the user button the information from the userform is then transferred to the db fields as above. And an automatically generated callref num is allocated and saved to call_ref_num.

I have since created a search form in which once the call_ref_num is populated, and they press search, what needs to happen is that the orginial userform (USERFORM1) should open and be automatically populated with the appropriate data from the db based on the

I desperately need help to make this happen.

Any help is appreciated...

Cheers
PJ

 

[al_b_cnu]

Hi, The Worksheet_BeforeDoubleClick event should do you to place the Userform Show statement.

The tricky part is to determine which data goes in which textbox. Maybe if you have your textboxes and Labels named 'TextBox1, Label1, TextBox2, Label2 etc with the number matching the data column in your database sheet, you could use something like this:

Private Sub UserForm_Activate()
Dim Ctl As Control
Dim iCol As Integer

For Each Ctl In Me.Controls
    If Left$(Ctl.Name, 7) = "TextBox" Then
        iCol = Val(Mid$(Ctl.Name, 8))
        Me.Controls("Label" & iCol).Caption = ActiveSheet.Cells(1, iCol).Value
        Ctl.Text = ActiveSheet.Cells(Selection.Row, iCol).Value
    End If
Next Ctl
End Sub

hi PJ,

I think you need to amend the above code to read:

Private Sub UserForm_Activate()
Dim Ctl As Control
Dim iCol As Integer

For Each Ctl In Me.Controls
    If Left$(Ctl.Name, 7) = "TextBox" Then
        iCol = Val(Mid$(Ctl.Name, 8))
        Me.Controls("Label" & iCol).Caption = Sheets("DB").Cells(1, iCol).Value
        Ctl.Text = ActiveSheet.Cells(Selection.Row, iCol).Value
    End If
Next Ctl
End Sub