Start Free Trial

asked on

Search Access Database and populate excel Userform

Hi,

Guys I know I will probably get a lot of questions why I am doing this in Excel and not access. The reason is because we have several users and company will not purchase additional licenses for PC's.

My userform already sends data to access table when the user enter the data and click submit. What I am trying to do now is search the database for records and the results populate in the excel userform so we can make edits to the data.

I added a 2 text box to my form that the users enter the date and the branch no they wish to search. I and not sure how to change the code to accept both criteria. Currently I have just the date field, but I get a data mismatch when I click the search button. Can you help me get this running?

Thanks

Private Sub CommandButton4_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim searchstring As String
    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Ace.OLEDB.12.0; " & _
        "Data Source=C:\Users\Desktop\TestDBA\Tracking_be.accdb"
    ' open a recordset
    Set rs = New ADODB.Recordset
    search = txtDate.Value
    search2 = txtBranchNo.value
    searchstring = "SELECT businessDate, advances, advanceAmount, denied, newAccounts, stopPayments,wires, cpg,branchLoc FROM tblMsrTotals WHERE [businessDate] = '" & search & "'"
    rs.Open "tblMsrTotals", searchstring, cn, adOpenStatic
    txtbusinessDate.Text = rs.Fields("businessDate") 'or whatever
    txtadvances.Text = rs.Fields("advances") 'or whatever
    txtadvanceAmount.Text = rs.Fields("advanceAmount") 'or whatever
    txtdenied.Text = rs.Fields("denied") 'or whatever
    txtnewAccounts.Text = rs.Fields("newAccounts") 'or whatever
    txtstopPayments.Text = rs.Fields("stopPayments") 'or whatever
    txtwires.Text = rs.Fields("wires") 'or whatever
    txtcpg.Text = rs.Fields("cpg") 'or whatever
    txtbranchLoc.Text = rs.Fields("branchLoc") 'or whatever
        
    Set rs = Nothing

End Sub

Open in new window

Try changing this part:

 searchstring = "SELECT businessDate, advances, advanceAmount, denied, newAccounts, stopPayments,wires, cpg,branchLoc FROM tblMsrTotals WHERE [businessDate] = #" & search & "#"

Open in new window

ASKER

Wow, Thanks for such a quick response. I tried your change, but still debug at
" rs.Open "tblMsrTotals", searchstring, cn, adOpenStatic" Data type mismatch. Should I be able to but both the date and branch number when searching?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Actually I fixed this line

rs.Open "tblMsrTotals", searchstring, cn, adOpenStatic

Open in new window

with

rs.Open searchstring, cn, adOpenStatic

Open in new window

Now I get a compile error: Invalid use of null at

 txtdenied.Text = rs.Fields("denied")

Open in new window

Some of the record fields are blank, but I still want to pull the record.

Use the Nz function:

txtdenied.Text = Nz(rs.Fields("denied"))

Open in new window

ASKER

Sorry, I must have been typing while you sent response. But I am thinking it wont pull record because of null values. I have i text box for date and one for Branch, so how will I search for both?

The Nz function will change it to a zero-length string so it should work.
Nz(rs.Fields("denied"))
or
Nz(rs.Fields("denied"),"")

ASKER

I got a compile error at txtdenied.Text = Nz(rs.Fields("denied"))
sub or function not defined. That field is a checkbox if that makes any difference.

ASKER

I am confusing myself. Its not a checkbox, but still get the compile error ..sub or function not defined.

Oh, you're in Excel. :-(
Since Excel doesn't have the Nz function you could try to see if the IsBlank Function will work.

txtdenied.Text = If(IsBlank(rs.Fields("denied")),"",rsFields("denied"))

Open in new window

To search for Branch location as well, try this:

 searchstring = "SELECT businessDate, advances, advanceAmount, denied, newAccounts, stopPayments,wires, cpg,branchLoc FROM tblMsrTotals WHERE [businessDate] = #" & search & "# And [brandLoc]='" & search2 & "'"

Open in new window

ASKER

it didn't like the code below. syntax error

 txtdenied.Text = If(IsBlank(rs.Fields("denied")),"",rsFields("denied"))

Open in new window

I forgot one "i" in the IIF

 txtdenied.Text = IIF(IsBlank(rs.Fields("denied")),"",rsFields("denied"))

Open in new window

ASKER

still get the compile error ..sub or function not defined.........I really appreciate the help with this.

Okay, I guess IsBlank only works within cell formulas, try replacing IsBlank with IsNull

 txtdenied.Text = IIF(IsNull(rs.Fields("denied")),"",rsFields("denied"))

Open in new window

Or

 txtdenied.Text = IIF(rs.Fields("denied")=NULL),"",rsFields("denied"))

Open in new window

ASKER

didn't like either one.....says expected: end of statement

Is the error on that line or somewhere else?

ASKER

The error is on the same line.

 txtdenied.Text = IIF(rs.Fields("denied")=NULL),"",rsFields("denied"))

Open in new window

Oops, had an extra parenthesis.

txtdenied.Text = IIF(rs.Fields("denied")=NULL,"",rsFields("denied"))

Open in new window

ASKER

I got it to work, with the code below, but only with the date....I have a second text box that I need to enter the branch number. Can you assist me with including the 2nd text box called txtsearchBranch

 
search = txtSearch.Value
search2=txtSearchBranch.Value
searchstring = "SELECT businessDate, advances, advanceAmount, denied, newAccounts, stopPayments,wires, cpg,branchLoc FROM tblMsrTotals WHERE [businessDate] = #" & search & "#"
    
 With rs
    rs.Open searchstring, cn, adOpenStatic
      txtbusinessDate.Text = !businessDate.Value & ""
    txtadvances.Text = !advances.Value & ""
    txtadvanceAmount.Text = !advanceAmount.Value & ""
    txtdenied.Text = !denied.Value & ""
    txtnewAccounts.Text = !newAccounts.Value & ""
    txtstopPayments.Text = !stopPayments.Value & ""
    txtwires.Text = !wires.Value & ""
    txtcpg.Text = !cpg.Value & ""
    txtbranchLoc.Text = !branchLoc.Value & ""
    

.Close

End With
        
    Set rs = Nothing

End Sub

Open in new window

search = txtSearch.Value
searchstring = "SELECT businessDate, advances, advanceAmount, denied, newAccounts, stopPayments,wires, cpg,branchLoc FROM tblMsrTotals WHERE [businessDate] = #" & search & "#"
    
 With rs
    rs.Open searchstring, cn, adOpenStatic
      txtbusinessDate.Text = !businessDate.Value & ""
    txtadvances.Text = !advances.Value & ""
    txtadvanceAmount.Text = !advanceAmount.Value & ""
    txtdenied.Text = !denied.Value & ""
    txtnewAccounts.Text = !newAccounts.Value & ""
    txtstopPayments.Text = !stopPayments.Value & ""
    txtwires.Text = !wires.Value & ""
    txtcpg.Text = !cpg.Value & ""
    txtbranchLoc.Text = !branchLoc.Value & ""
    

.Close

End With
        
    Set rs = Nothing

End Sub

Open in new window

 
search = txtSearch.Value
search2=txtSearchBranch.Value
searchstring = "SELECT businessDate, advances, advanceAmount, denied, newAccounts, stopPayments,wires, cpg,branchLoc FROM tblMsrTotals WHERE [businessDate] = #" & search & "#"
    
 With rs
    rs.Open searchstring, cn, adOpenStatic
      txtbusinessDate.Text = !businessDate.Value & ""
    txtadvances.Text = !advances.Value & ""
    txtadvanceAmount.Text = !advanceAmount.Value & ""
    txtdenied.Text = !denied.Value & ""
    txtnewAccounts.Text = !newAccounts.Value & ""
    txtstopPayments.Text = !stopPayments.Value & ""
    txtwires.Text = !wires.Value & ""
    txtcpg.Text = !cpg.Value & ""
    txtbranchLoc.Text = !branchLoc.Value & ""
    

.Close

End With
        
    Set rs = Nothing

End Sub

Open in new window

The post I had earlier didn't work?
earlier post

ASKER

No... code below still didn't work. same compile error.

     txtdenied.Text = IIf(rs.Fields("denied") = Null, "", rsFields("denied"))

Open in new window

I meant my earlier post about searching for Branch location as well. Here's a link to it:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27776277.html#a38141741

ASKER

No that didnt work. The user enters the branch number in a text box called txtSearchBranch and the date in a textbox called txtSearch.

I added search2=txtSearchBranch.Value for the Branch no.

Well, search2 is what I had in the code. Here it is again:

 searchstring = "SELECT businessDate, advances, advanceAmount, denied, newAccounts, stopPayments,wires, cpg,branchLoc FROM tblMsrTotals WHERE [businessDate] = #" & search & "# And [brandLoc]='" & search2 & "'"

Open in new window

ASKER

I get compile error: object required at

search2 = txtSearchBranch.Value

Open in new window

Are you certain that is the name of your control? Earlier you had
search2 = txtBranchNo.value

ASKER

Ok, figured out what I did wrong. I copied your solution, but it had a typo. Should have been BranchLoc instead of BrandLoc.

ASKER

now getting a data type mismatch. Is it because of the branchLoc which is a numeric field?

Brand, Branch, same difference :-) lol

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

I did change the control name. so what i have below is correct. Getting a data mismatch.

search2 = txtSearchBranch.Value

Is the mismatch right on the line search2 = txtSearchBranch.Value or is it on rs.open?

ASKER

Thank you so much for your help. Works perfect now. Not sure what I would do with you folks on here. So much patients and understanding. Thank you again.