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

Microsoft ExcelVisual Basic ClassicMicrosoft Access

Avatar of undefined
Last Comment
KLM56

8/22/2022 - Mon
IrogSinta

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

Open in new window

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
IrogSinta

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
IrogSinta

Use the Nz function:
txtdenied.Text = Nz(rs.Fields("denied"))

Open in new window

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?
IrogSinta

The Nz function will change it to a zero-length string so it should work.
Nz(rs.Fields("denied"))
or
Nz(rs.Fields("denied"),"")
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
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.
KLM56

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

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

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
IrogSinta

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

KLM56

ASKER
it didn't like the code below. syntax error

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

Open in new window

IrogSinta

I forgot one "i" in the IIF
 txtdenied.Text = IIF(IsBlank(rs.Fields("denied")),"",rsFields("denied"))

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
KLM56

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

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

KLM56

ASKER
didn't like either one.....says expected: end of statement
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
IrogSinta

Is the error on that line or somewhere else?
KLM56

ASKER
The error is on the same line.

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

Open in new window

IrogSinta

Oops, had an extra parenthesis.
txtdenied.Text = IIF(rs.Fields("denied")=NULL,"",rsFields("denied"))

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
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

IrogSinta

The post I had earlier didn't work?
earlier post
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

Your help has saved me hundreds of hours of internet surfing.
fblack61
IrogSinta

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
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.
IrogSinta

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

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
KLM56

ASKER
I get compile error: object required at  
search2 = txtSearchBranch.Value

Open in new window

IrogSinta

Are you certain that is the name of your control?  Earlier you had
search2 = txtBranchNo.value
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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
KLM56

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

Brand, Branch, same difference :-) lol
SOLUTION
IrogSinta

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
IrogSinta

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
KLM56

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

search2 = txtSearchBranch.Value
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
IrogSinta

Is the mismatch right on the line search2 = txtSearchBranch.Value or is it on rs.open?
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.