Userform with MS Access Backend, Search tool 1

[cken21]

Hi,

I have been searching forums and google all week to try and crack this, and i think i am most of the way there. I have an excel userform which currently collects data through Textboxes, Combo boxes, Checkboxes etc. What i need to do is pull this information off the database and display it on my userform - This part i have cracked with the following code:

Search = tbSearch.Value


Set rs = New ADODB.Recordset
searchstring = "SELECT ProductNum, Brand, FeedbackFrom FROM SwitchingFeedbackLog WHERE [ProductNum] = '" & Search & "'"
rs.Open searchstring, cn, adOpenStatic
tb1.Text = rs.Fields("ProductNum") 'or whatever
tb2.Text = rs.Fields("Brand") 'or whatever
tb3.Text = rs.Fields("FeedbackFrom") 'or whatever
Set rs = Nothing

However i am lost on how to edit this record once displayed, and if possible delete it. Any help or tips would be greatly appreciated.

 

[Andrzejek]

How about:
Update (edit) record:

Private Sub cmdUpdate()
Dim strUpdate As String

strUpdate = "Update SwitchingFeedbackLog Set ProductNum = " & tb1.Text & ", Brand = '" & tb2.Text & "', FeedbackFrom = '" & tb3.Text & "' Where ...."

cn.Execute strUpdate 

End Sub

Delete the record:

Private Sub cmdDelete()
Dim strDelete As String

strDelete = "Delete from SwitchingFeedbackLog Where ProductNum = " & tb1.Text & " and Brand = '" & tb2.Text & "' and FeedbackFrom = '" & tb3.Text & "'"

cn.Execute strDelete

End Sub

Of course you need to do some validation for single quote in text, make sure there are numbers where numbers should be, that you update just the record you want to update, the same goes for Deleting the right (one?) record, etc.

Have fun.

---- Andy

 

[cken21]

Thanks both those worked great. I have one question though, how would i display/update values to a combo box?

 

[Andrzejek]

Display stuff in combo box - easy: see the example below.

I placed a combo box cbProductNum on the Form, you may want to set its Style to 2 - fmStyleDropDownList if you do not want your user to change ProductNum

Option Explicit
Dim rs As ADODB.Recordset

Private Sub UserForm_Activate()
Dim strSQL As String

strSQL = "Select Distinct ProductNum From SwitchingFeedbackLog Order by ProductNum"

Set rs = New ADODB.Recordset
cbProductNum.Clear

With rs
    .Open strSQL, cn, adOpenForwardOnly
    Do While Not .EOF
        cbProductNum.AddItem !ProductNum.Value & ""
        .MoveNext
    Loop
    .Close
End With
Set rs = Nothing

If cbProductNum.ListCount > 0 Then
    cbProductNum.ListIndex = 0
End If

End Sub

Private Sub cbProductNum_Click()

Set rs = New ADODB.Recordset
strSQL = "SELECT Brand, FeedbackFrom FROM SwitchingFeedbackLog WHERE [ProductNum] = '" & cbProductNum.Text & "'"[green]
'Assuming 1 record per ProductNum[/green]
With rs
    .Open strSQL, cn, adOpenStatic
    tb2.Text = .Fields("Brand").Value
    tb3.Text = .Fields("FeedbackFrom").Value
End With
Set rs = Nothing

End Sub

"update values to a combo box?" - you would need to explain what you mean by that.


Have fun.

---- Andy

 

[Andrzejek]

Oops, you may want to move blue like of code since I did not test the code:

Option Explicit
Dim rs As ADODB.Recordset[blue]
Dim strSQL As String[/blue]

Private Sub UserForm_Activate()
...

Have fun.

---- Andy

 

[cken21]

Thanks Andy, this has all been great. I have one last question. When i am searching for the record i need it will not display the null values - is there any way around that?

e.g

Initially the user will only complete 1 field tb1,

the next user needs to search using this value to return the blank tb2 and tb3 at which point he will update the record. I hope im explaining this well enough

 

[Andrzejek]

So you do want to display NULLs or you do NOT want NULLs in ProductNum? If NOT, then:

strSQL = "Select Distinct ProductNum From SwitchingFeedbackLog [blue]Where ProductNum Is Not Null [/blue]Order by ProductNum"

I would also use some other, more discriptive names for your text boxes, like txtBrand instead of tb2

Have fun.

---- Andy

 

[cken21]

Sorry, this is my fault. I havent explained it correctly. The actual code i am using looks like this

Set rs = New ADODB.Recordset
searchstring = "SELECT ScriptID, CaseID, Progress, Wizard, EForm, DateStart, PEGA, Overall, DateComplete, Defects, Comments FROM PETesting WHERE [CaseID] = '" & Search & "'"
rs.Open searchstring, cn, adOpenStatic
tbScriptID.Text = rs.Fields("ScriptID")
tbCaseID.Text = rs.Fields("CaseID")
tbProgress.Text = rs.Fields("Progress")
tbWizard.Text = rs.Fields("Wizard")
tbEForm.Text = rs.Fields("EForm")
tbDateStart.Text = rs.Fields("DateStart")
tbPEGA.Text = rs.Fields("PEGA")
tbOverall.Text = rs.Fields("Overall")
tbDateComplete.Text = rs.Fields("DateComplete")
tbDefects.Text = rs.Fields("Defects")
tbComments.Text = rs.Fields("Comments")

The bottom 6 fields will be empty after the record is initially logged. I need to search for the CaseID and display the full record, which works fine if all fields have values. I get the error when the bottom 6 fields are null..is there a way around this?

 

[Andrzejek]

Of course there is a way around this.
You get an error because you try to put NULL into a text box, and that's a no-no. The easiest way to avoid that is to add an empty string to the rs field, like this:
(and since you do not have any spaces in the names of the fields - good for you! - you can have code like this))

    Set rs = New ADODB.Recordset
    searchstring = "SELECT ScriptID, CaseID, Progress, Wizard, EForm, DateStart, PEGA, Overall, DateComplete, Defects, Comments FROM PETesting WHERE [CaseID] = '" & Search & "'"

With rs
    .Open searchstring, cn, adOpenStatic
    tbScriptID.Text = !ScriptID[blue].Value & ""[/blue]
    tbCaseID.Text = !CaseID[blue].Value & ""[/blue]
    tbProgress.Text = !Progress[blue].Value & ""[/blue]
    tbWizard.Text = !Wizard[blue].Value & ""[/blue]
    tbEForm.Text = !EForm[blue].Value & ""[/blue]
    tbDateStart.Text = !DateStart[blue].Value & ""[/blue]
    tbPEGA.Text = !PEGA[blue].Value & ""[/blue]
    tbOverall.Text = !Overall[blue].Value & ""[/blue]
    tbDateComplete.Text = !DateComplete[blue].Value & ""[/blue]
    tbDefects.Text = !Defects[blue].Value & ""[/blue]
    tbComments.Text = !Comments[blue].Value & ""[/blue]
End With

Have fun.

---- Andy

 

[cken21]

Hi,

Sorry for the late reply here. That has all worked brilliantly you really helped me out here.

Cheers

 

[Andrzejek]

I am glad

In my last post I did not have:

Set rs = New ADODB.Recordset
...
With rs
    .Open searchstring, cn, adOpenStatic
    ...    [blue]
    .Close [/blue] 
End With[blue]
Set rs = Nothing[/blue]

Have fun.

---- Andy