Help with Search Database

[SFDemon]

I am new to Access and have no idea of what I'm doing. Help Please!!!

I'm working on a project to keep track of payments to loans.

This is a somewhat simple table that I would like to query by any of the fields (exept the Reason field)

I found a Sample DB that works great for what I would like to do. I'm just having a hard time figuring out how it all works so that I can modify to work with my table.

Also, every month I will get an Excel spreadsheet with all of the information on my access table with hundreds of records. What is the best way to upload this information to my Access DB?

The Database I found was created by Allen Browne. He created a form with different fields that allow him to search by entering as little or as much information as one might have. I like that fact that if all I have is just any one piece of information I can search my database or pull a date range of activity.

While in design View I know I can delete the search fields. but how do I add new fields and link them through VBA to search my table? I will upload my table and Allen's Sample DB. Any help you can provide would be greatly appreciated.

 

[hgus393]

SFDemon,
Check this out as a start perhaps.
Cheers
Bob

 

[hgus393]

A newer version with an add record functionality.

BOB

 

[hgus393]

Ok final version with a delete record button and an excel import button. Note for the delete record button the record needs to be marked to be deleted i.e. choose record and press button. For the Excel Import button I have done the following:
A macro that checks in a specific folder and imports everything (excel) that is in that folder and in a tab called Loans. Please see code below:

Sub GetLoans(sPath As String, sFile As String, MyXl As Object)
    Dim rs As Recordset
    Dim i As Integer
    Dim VVector As Variant
    Dim sSheet As String
 
    Set MyXl = GetObject(sPath & sFile)
 
     sSheet = "Loans"
 
    Set rs = CurrentDb.OpenRecordset("Loans")
    i = 2
    VVector = MyXl.Worksheets(sSheet).Range("A" & i & ":F" & i).Value ' I have based the columns on your database
 
    Do While VVector(1, 1) <> ""
        rs.AddNew
        rs![Loan Number] = VVector(1, 1)
        rs![Payment Code] = VVector(1, 2)
        rs![Amount] = VVector(1, 3)
        rs![Reason] = VVector(1, 4)
        rs![Received by] = VVector(1, 5)
        rs![Date Received] = VVector(1, 6)
        rs.Update
        i = i + 1
        VVector = MyXl.Worksheets(sSheet).Range("A" & i & ":F" & i).Value
    Loop
 
    MyXl.Close SaveChanges:=False
End Sub

and in the forms code:

'------------------------------------------------
'Code gets all books in specific catalog, thus
'enabling mutiple imports.
'Good Luck, Bob!
'------------------------------------------------
Private Sub ImportExcel_Click()
  Dim dtDate As Date
    Dim sPath As String, sFile As String
    Dim MyXl As Object
 
    sPath = "G:\Junk\" 'Change to suit your location
 
    If Dir(sPath) = "" Then
        MsgBox ("Catalog is missing!")
        Exit Sub
    End If
 
    sFile = Dir$(sPath & "*.xls")
    Set MyXl = CreateObject("Excel.Application")
 
    Do While sFile <> ""
        GetLoans sPath, sFile, MyXl
        sFile = Dir
    Loop
 
    Set MyXl = Nothing
 
    MsgBox ("Input to Access done!")
    Me.QLoanssubform.Requery ' refreshes form to show new records
End Sub

Enjoy Bob

 

[SFDemon]

hgus393 you are the BEST!

It looks great. I will have to look at the code and try to make sense of it. I would like to learn how it all works. However, I'm getting an error whenever I try to search for "Received By" field. It says Ryn-time Error '3075': Syntax error (missing operator) in query expression'[Recieved by]

Also, if I try to search for date received I get no matches and I know that the date entered is in the table.

THANK YOU!!!!! for your help.

 

[hgus393]

hgus393 you are the BEST!

It looks great. I will have to look at the code and try to make sense of it. I would like to learn how it all works. However, I'm getting an error whenever I try to search for "Received By" field. It says Ryn-time Error '3075': Syntax error (missing operator) in query expression'[Recieved by]

Also, if I try to search for date received I get no matches and I know that the date entered is in the table.

THANK YOU!!!!! for your help.

You're welcome!
I have fixed the problems, misspelled the field ...fixed it. Also fixed the date filter, tested it and it works.
Cheers
Bob
I cannot upload the database - seems to be a problem just now. post your email address and I'll mail it to you instead.

 

[raskew]

Hi -

The attached form, based on the Cool Search Tool concept (do a search on this forum) allows the user to search specified fields by entering the criteria into one text box. You can enter any portion of any field and, with each keystroke, the search function will drill down to those records containing the inputted criteria.

I wrote it in A97, but it should convert to later versions without problems.

HTH - Bob

 

[SFDemon]

hgus393,

Wow that was Super Fast! thank you! my e-mail is SFDemon@Gmail.com

Thanx raskew:

I'll take a look at it.

I'm trying to learn as much as possible. I have never had the need for Access before and now that I need it I know practically nothing, but I'm sure I can learn it with some dedication.

This Forum is awsome!!!

 

[SFDemon]

hgus393,

I have created an Excel file with same info as table ( exept I do not have a ID column)

This is how it looks:
Row 1: Column Titles

A1 - Loan Number
B1 - Payment Code
C1 - Amount
D1 - Reason
E1 - Received by
F1 - Date Received

I called this Exel file "Loantest"
file has been placed at C:\Temp\

So this is how I modified the VBA you wrote:

Sub GettingLoans()
Dim dtDate As Date
Dim sPath As String, sFile As String, sql As String
Dim MyXl As Object

sPath = "C:\Temp\" ' Change to suit your own needs

If Dir(sPath) = "" Then
MsgBox ("Catalog missing!")
Exit Sub
End If

However, I when I try to upload I keep getting the "Catalog missing!" error.

Is there something else that I need to change?

Thank you!!

 

[SFDemon]

Hi -

The attached form, based on the Cool Search Tool concept (do a search on this forum) allows the user to search specified fields by entering the criteria into one text box. You can enter any portion of any field and, with each keystroke, the search function will drill down to those records containing the inputted criteria.

I wrote it in A97, but it should convert to later versions without problems.

HTH - Bob

raskew, thanks a lot. Very cool DB. I guess there is more than one way to skin a cat!

 

[hgus393]

hgus393,

I have created an Excel file with same info as table ( exept I do not have a ID column)

This is how it looks:
Row 1: Column Titles

A1 - Loan Number
B1 - Payment Code
C1 - Amount
D1 - Reason
E1 - Received by
F1 - Date Received

I called this Exel file "Loantest"
file has been placed at C:\Temp\

So this is how I modified the VBA you wrote:

Sub GettingLoans()
Dim dtDate As Date
Dim sPath As String, sFile As String, sql As String
Dim MyXl As Object

sPath = "C:\Temp\" ' Change to suit your own needs

If Dir(sPath) = "" Then
MsgBox ("Catalog missing!")
Exit Sub
End If

However, I when I try to upload I keep getting the "Catalog missing!" error.

Is there something else that I need to change?

Thank you!!

Hum,
Yeah I have noticed that too..tested below and it works
H:\My Documents\Excel Files\ ....or something like that
Bob

 

[hgus393]

Hi -

The attached form, based on the Cool Search Tool concept (do a search on this forum) allows the user to search specified fields by entering the criteria into one text box. You can enter any portion of any field and, with each keystroke, the search function will drill down to those records containing the inputted criteria.

I wrote it in A97, but it should convert to later versions without problems.

HTH - Bob

Nice!!
I like the way it drills down..very nice!
Bob

 

[hgus393]

hgus393,

I have created an Excel file with same info as table ( exept I do not have a ID column)

This is how it looks:
Row 1: Column Titles

A1 - Loan Number
B1 - Payment Code
C1 - Amount
D1 - Reason
E1 - Received by
F1 - Date Received

I called this Exel file "Loantest"
file has been placed at C:\Temp\

So this is how I modified the VBA you wrote:

Sub GettingLoans()
Dim dtDate As Date
Dim sPath As String, sFile As String, sql As String
Dim MyXl As Object

sPath = "C:\Temp\" ' Change to suit your own needs

If Dir(sPath) = "" Then
MsgBox ("Catalog missing!")
Exit Sub
End If

However, I when I try to upload I keep getting the "Catalog missing!" error.

Is there something else that I need to change?

Thank you!!

Hi again,
As long as your tab in excel is called Loans then there should be no more changes needed. You don't need the autonumber access fixes that all on its own.

Bob

 

[qcrepro]

Bob,

I reviewed your LoanDB97. I really like the search functionality. I have been trying to do something similar but cannot find the right code. Can you take a look a my file and see if this can be implemented. I would like the user to be able to click a button on the switchboard and have it load a search like yours.

Thanks

Scott

 

[hgus393]

Bob,

I reviewed your LoanDB97. I really like the search functionality. I have been trying to do something similar but cannot find the right code. Can you take a look a my file and see if this can be implemented. I would like the user to be able to click a button on the switchboard and have it load a search like yours.

Thanks

Scott

Hi Scott,
It was not I who did the loandb97 but Raskew... However, what did you have in mind? What functionality? What table(s) or query(ies) would you like to use as a starting point? Is it the Account Survey Query?
Bob

 

[qcrepro]

Bob,

Sorry I thought it was you that had created the database. What I am trying to achieve is something similiar to frmLoans. I would like the user to be able to enter any text/number and have it search all fields of the account survey query. After entering the search criteria I need some way to export the information into an excel spreadsheet.

Any Help.


Thanks

Scott

 

[qcrepro]

Hi -

The attached form, based on the Cool Search Tool concept (do a search on this forum) allows the user to search specified fields by entering the criteria into one text box. You can enter any portion of any field and, with each keystroke, the search function will drill down to those records containing the inputted criteria.

I wrote it in A97, but it should convert to later versions without problems.

HTH - Bob

Raskew,

I am trying to acheive similar search functionality you have in your LoanDB97. I attached my database in a previous post. I would like the user to be able to enter any search criteria and have it search all field of the account survery query. Any insight would be appreciated.

Scott