Link to home
Create AccountLog in
Avatar of KLM56
KLM56

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

Avatar of IrogSinta
IrogSinta
Flag of United States of America image

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

Open in new window

Avatar of KLM56
KLM56

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
Avatar of IrogSinta
IrogSinta
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of KLM56

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

Avatar of KLM56

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"),"")
Avatar of KLM56

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.
Avatar of KLM56

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

Avatar of KLM56

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

Avatar of KLM56

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

Avatar of KLM56

ASKER

didn't like either one.....says expected: end of statement
Is the error on that line or somewhere else?
Avatar of KLM56

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

Avatar of KLM56

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
Avatar of KLM56

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
Avatar of KLM56

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

Avatar of KLM56

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
Avatar of KLM56

ASKER

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

ASKER

now getting a data type mismatch. Is it because of the branchLoc which is a numeric field?
Brand, Branch, same difference :-) lol
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of KLM56

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?
Avatar of KLM56

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.