Search an Access DB

[AricC]

Hello Access experts. Does anyone have an example of how I search a DB using a text box then fill in a form with the results?

TIA,
Aric

[AricC]

Please disregard for now I think I have found a decent example that will do the job.

Aric

[AricC]

Nevermind still a bit lost on this subject it seems like I can find a ton of examples of people doing a search with 1 table but not several relational tables. Any help would be appreciated!


Thanks,
Aric

[MMcCarthy]

Nevermind still a bit lost on this subject it seems like I can find a ton of examples of people doing a search with 1 table but not several relational tables. Any help would be appreciated!


Thanks,
Aric

Aric design a query to use as a basis for a form returning all results. For this example form is "SearchResults"

Then on another form put your textbox and a command button called cmdSearch.

Then in the code behind you button put

Private Sub cmdSearch_Click()
 
   DoCmd.OpenForm "SearchResults", , ,"[ControlName]=" & Me.textboxName
 
End Sub
 

Ask me about anything you don't understand. You will have to change [ControlName] to the name of the control on the "SearchResults" form and textboxName to the name of your textbox on your search form.

The above example will work for numerical fields, otherwise.

For text fields

"[ControlName]='" & Me.textboxName & "'"

For Date fields

"[ControlName]=#" & Me.textboxName & "#"

Mary

[AricC]

I'll give that a whirl thanks! I'm sure I'll have some questions.

Thanks :),
Aric

[MMcCarthy]

I'll give that a whirl thanks! I'm sure I'll have some questions.

Thanks :),
Aric

np

BTW check out the xmas part thread. see the announcement on top of all forums.

Mary

[NeoPa]

I'm sure Mary just does this to give me posting opportunities...
You should really format the date literals as 'm/d/yyyy' to avoid mismatched dates.

"[ControlName]=" & Format(Me.textboxName,'\#m/d/yyyy\#')

See (Literal DateTimes and Their Delimiters (#).) for more info.

[MMcCarthy]

I'm sure Mary just does this to give me posting opportunities...
You should really format the date literals as 'm/d/yyyy' to avoid mismatched dates.

"[ControlName]=" & Format(Me.textboxName,'\#m/d/yyyy\#')

See (Literal DateTimes and Their Delimiters (#).) for more info.

Sorry Ade, I don't know why I keep forgetting that bit when it's a bug fix. My bad.

Mary

[AricC]

Thanks, both of you, my crappy work PC died yesterday. Which isn't bad since I am getting a brand new one. Also, it will allow me to take a break from this 'Project From Hell'

Thanks,
Aric

[MMcCarthy]

Thanks, both of you, my crappy work PC died yesterday. Which isn't bad since I am getting a brand new one. Also, it will allow me to take a break from this 'Project From Hell'

Thanks,
Aric

Are you sure you had nothing to do with this death?

[AricC]

Are you sure you had nothing to do with this death?

Lol just revisited this thread and saw this. Very funny. No, I did nothing to harm that machine. I've had some terrible luck; 2 have died on me I wish they would quit giving me hand-me-downs that work when they want. The last one that died was a P2 400 mhz 256 memory it was one of the most heinous machines I've worked with ( it rebooted every hour or so ).

[AricC]

Ok I'm getting closer, I am running this query based on a forms text box. I then am opening the form where I want the values, however nothing on the form has values.

Private Sub btnSearch_Click()
Dim dbRepairs As DAO.Database
Dim qrySearchSerialNumbers As DAO.QueryDef
Dim strSQL As String
Set dbRepairs = CurrentDb
Set qrySearchSerialNumbers = dbRepairs.QueryDefs("qrySearchSerialNumbers")
 
strSQL = "SELECT TRepairs.*, TSerialNumbers.strSerialNumber " & _
            "FROM TRepairs INNER JOIN TSerialNumbers ON TRepairs.intSerialNumberID=TSerialNumbers.intSerialNumberID " & _
            "WHERE TSerialNumbers.strSerialNumber ='" & [Forms]![frmSearchSerialNumber]![txtSerialNumber] & "';"
 
qrySearchSerialNumbers.SQL = strSQL
DoCmd.OpenQuery "qrySearchSerialNumbers"
 
DoCmd.OpenForm "frmSearchResults", , , "[txtSerialNumber]='" & Me.txtSerialNumber.Value & "'" 'etc..........
 
 
 
Set qrySearchSerialNumbers = Nothing
Set dbRepairs = Nothing
 
End Sub

TIA,
Aric

Note: intSeria lNumberID doesn't have extra spaces inbetween, just shows that way.

[NeoPa]

I think I know what you mean...
You want the query to populate the form for you?
If so, you need to set the Record Source of the form to the query (or SQL) rather than running the query separately before opening the form.

[AricC]

Yes, that's what I want to do populate the form from a query. Do you have any links with examples NeoPa?

Thanks,
Aric

[NeoPa]

No - no examples.
It should be easier than that though. Just set the RecordSource and/or Filter and AllowFilters properties to the QueryDef (saved query) or SQL that you require.
Let me know if this answers your question.

[AricC]

No not following kinda new with Access. I'll look around see if I can find some examples.

[bartonc]

Ok I'm getting closer, I am running this query based on a forms text box. I then am opening the form where I want the values, however nothing on the form has values.

Private Sub btnSearch_Click()
Dim dbRepairs As DAO.Database
Dim qrySearchSerialNumbers As DAO.QueryDef
Dim strSQL As String
Set dbRepairs = CurrentDb
Set qrySearchSerialNumbers = dbRepairs.QueryDefs("qrySearchSerialNumbers")
 
strSQL = "SELECT TRepairs.*, TSerialNumbers.strSerialNumber " & _
            "FROM TRepairs INNER JOIN TSerialNumbers ON TRepairs.intSerialNumberID=TSerialNumbers.intSerialNumberID " & _
            "WHERE TSerialNumbers.strSerialNumber ='" & [Forms]![frmSearchSerialNumber]![txtSerialNumber] & "';"
 
qrySearchSerialNumbers.SQL = strSQL
DoCmd.OpenQuery "qrySearchSerialNumbers"
 
DoCmd.OpenForm "frmSearchResults", , , "[txtSerialNumber]='" & Me.txtSerialNumber.Value & "'" 'etc..........
 
 
 
Set qrySearchSerialNumbers = Nothing
Set dbRepairs = Nothing
 
End Sub

TIA,
Aric

Note: intSeria lNumberID doesn't have extra spaces inbetween, just shows that way.

Ah-ha. Someone else is getting broken lines in code tags. Maybe not an IE user either...

[AricC]

Ah-ha. Someone else is getting broken lines in code tags. Maybe not an IE user either...

I use IE for the most part.

[NeoPa]

No not following kinda new with Access. I'll look around see if I can find some examples.

Sorry Aric.
I guess you have a QueryDef (Saved Query) or some SQL that you want to use right?
Well, to get the Form to use this as it's datasource you Open the form in Design view; Open the Properties window (for the form); In the Record Source property enter either the name of the QueryDef or the actual SQL desired.
This will then 'build' the form around the specified QueryDef or SQL query.

[AricC]

Sorry Aric.
I guess you have a QueryDef (Saved Query) or some SQL that you want to use right?
Well, to get the Form to use this as it's datasource you Open the form in Design view; Open the Properties window (for the form); In the Record Source property enter either the name of the QueryDef or the actual SQL desired.
This will then 'build' the form around the specified QueryDef or SQL query.

Thanks, I'll try that out!

Thanks for the help!
Aric

[MMcCarthy]

Thanks, I'll try that out!

Thanks for the help!
Aric

Set the record souce of the form to 'qrySearchSerialNumbers'

Then use the code you previously outlined but without the openquery line.

This should work.

Mary

[AricC]

I'll give it a whirl thanks Mary & NeoPa!

Aric

[AricC]

Set the record souce of the form to 'qrySearchSerialNumbers'

Then use the code you previously outlined but without the openquery line.

This should work.

Mary

Ok so I set the record source of my form ("frmSearchResults") to the query ("qrySearchSerialNumbers") how do I tell the form where to get the values, and how to put them back on the form? I've tried the following, but this method asks me for a parameter when opening the form then opens the form blank.

Thanks,
Aric

Private Sub btnSearch_Click()
Dim dbRepairs As DAO.Database
Dim qrySearchSerialNumbers As DAO.QueryDef
Dim strSQL As String
Set dbRepairs = CurrentDb
Set qrySearchSerialNumbers = dbRepairs.QueryDefs("qrySearchSerialNumbers")
 
strSQL = "SELECT TRepairs.*, TSerialNumbers.strSerialNumber " & _
            "FROM TRepairs INNER JOIN TSerialNumbers ON TRepairs.intSerialNumberID=TSerialNumbers.intSerialNumberID " & _
            "WHERE TSerialNumbers.strSerialNumber ='" & [Forms]![frmSearchSerialNumber]![txtSerialNumber] & "';"
 
qrySearchSerialNumbers.SQL = strSQL
'DoCmd.OpenQuery "qrySearchSerialNumbers"
DoCmd.OpenForm "frmSearchResults", , , "[txtSerialNumber]='" & Me.txtSerialNumber.Value & "'"
'DoCmd.Close acForm, Me.Name
Set qrySearchSerialNumbers = Nothing
Set dbRepairs = Nothing
 
End Sub
 

[NeoPa]

Private Sub btnSearch_Click()
...
strSQL = "SELECT TRepairs.*, TSerialNumbers.strSerialNumber " & _
            "FROM TRepairs INNER JOIN TSerialNumbers ON TRepairs.intSerialNumberID=TSerialNumbers.intSerialNumberID " & _
            "WHERE TSerialNumbers.strSerialNumber ='" & [Forms]![frmSearchSerialNumber]![txtSerialNumber] & "';"
End Sub

If the field in the record (intSerialNumberID) is an numeric variable (implied by the prefix int...) then the WHERE clause should not have quotes (') around the value part.

"WHERE TSerialNumbers.strSerialNumber=" & [Forms]![frmSearchSerialNumber].[txtSerialNumber] & ";"

Try running the query on its own to test that it produces valid (expected) results before binding it to a form.
If it doesn't work then it's a query issue rather than a form issue. This is v important to know and can save you a lot of trouble.
HTH -Adrian.

[AricC]

Thanks, yes when running alone and given a parameter the query runs as expected.

[AricC]

If the field in the record (intSerialNumberID) is an numeric variable (implied by the prefix int...) then the WHERE clause should not have quotes (') around the value part.

"WHERE TSerialNumbers.strSerialNumber=" & [Forms]![frmSearchSerialNumber].[txtSerialNumber] & ";"

Try running the query on its own to test that it produces valid (expected) results before binding it to a form.
If it doesn't work then it's a query issue rather than a form issue. This is v important to know and can save you a lot of trouble.
HTH -Adrian.

I'm not quite following above. The intSerialNumberID is the primary key, we want to return strSerialNumber (a text data type). Also, I've found a few examples where people are setting the record source of the form equal to some sql. This doesn't seem to work with my form as I am manually inserting the data using VBA.

Thanks NeoPa!
Aric

[NeoPa]

Thanks, yes when running alone and given a parameter the query runs as expected.

I'm coming in a bit late to this thread Aric. Why are you trying to set things up in code? Have you designed the form so that individual controls (some of them at least) are bound to the data source? That would seem to be a first step.
Also, you say 'and given a parameter'. Is this expected? What is the parameter it asks you for?

[NeoPa]

I'm not quite following above. The intSerialNumberID is the primary key, we want to return strSerialNumber (a text data type)

Thanks NeoPa!
Aric

Overlap of posting here - let's be careful.
Are you saying that you have two fields in the record - both called variations of 'SerialNumber'?
If so, then ignore the point I was making, as it won't be relevant in that case.

[AricC]

I'm coming in a bit late to this thread Aric. Why are you trying to set things up in code? Have you designed the form so that individual controls (some of them at least) are bound to the data source? That would seem to be a first step.
Also, you say 'and given a parameter'. Is this expected? What is the parameter it asks you for?

Currently, this is my query:

SELECT TRepairs.*, TSerialNumbers.strSerialNumber
FROM TSerialNumbers INNER JOIN TRepairs ON TSerialNumbers.intSerialNumberID = TRepairs.intSerialNumberID
WHERE (((TSerialNumbers.strSerialNumber)='SOME SERIAL NUMBER GOES HERE'));
 

When you run this alone you get asked for a parameter (a serial number). I enter a serial number and the data pops up in table form ( the data I want in the form for viewing editing ). In case I'm confusing here is a copy of my db.

[NeoPa]

One last thing to think about before I go home from work :
You are (in your code) updating the QueryDef that the form is built upon. This seems unnecessarily complicated when the only part of the SQL that is changed is the WHERE clause. It may be better to keep the form bound to the original QueryDef and pass a Filtering parameter to the form on opening.
I always recommend as little fiddling about with objects via code as possible. This results in fewer complications and misunderstandings when someone else (or even yourself at a later time) want to try to understand what's going on in a project. It would seem that there is scope for that here.

[AricC]

One last thing to think about before I go home from work
You are (in your code) updating the QueryDef that the form is built upon. This seems unnecessarily complicated when the only part of the SQL that is changed is the WHERE clause. It may be better to keep the form bound to the original QueryDef and pass a Filtering parameter to the form on opening.
I always recommend as little fiddling about with objects via code as possible. This results in fewer complications and misunderstandings when someone else (or even yourself at a later time) want to try to understand what's going on in a project. It would seem that there is scope for that here.

I want to go home as well
I didn't build the form based on a query. I built the form by opening the design view placing controls on the screen adding a button then putting code behind the button to add data etc... Access doesn't seem to like my methods :(

[NeoPa]

Currently, this is my query:

SELECT TRepairs.*, TSerialNumbers.strSerialNumber
FROM TSerialNumbers INNER JOIN TRepairs ON TSerialNumbers.intSerialNumberID = TRepairs.intSerialNumberID
WHERE (((TSerialNumbers.strSerialNumber)='SOME SERIAL NUMBER GOES HERE'));
 

When you run this alone you get asked for a parameter (a serial number). I enter a serial number and the data pops up in table form ( the data I want in the form for viewing editing ). In case I'm confusing here is a copy of my db.

Aric, Post overlap again I'm afraid.
A copy of your DB may well prove interesting - I'll download it when I get home.
In the mean time - would you mind finding answers to all of my recently posted questions. They may seem confused questions but they were carefully selected to throw as much light on 'my' confusion as possible.
In turn, I will do my best to throw light on the problems you're experiencing.
Catch you later from home. -Adrian.

[AricC]

I'm coming in a bit late to this thread Aric. Why are you trying to set things up in code? Have you designed the form so that individual controls (some of them at least) are bound to the data source? That would seem to be a first step.
Also, you say 'and given a parameter'. Is this expected? What is the parameter it asks you for?

When you run the query qrySearchSerialNumbers and are asked for a parameter you give the query 123 (123 being a serial number) when running the query from a form you aren't asked for a parameter; the parameter comes from a textbox on the form. I expect the parameter to be asked for when running standalone because there is nothing (no serial number) attached to the clause:

'qrySearchSerialNumbers
SELECT TRepairs.*, TSerialNumbers.strSerialNumber
FROM TRepairs INNER JOIN TSerialNumbers ON TRepairs.intSerialNumberID=TSerialNumbers.intSerialNumberID
WHERE TSerialNumbers.strSerialNumber='123';
 

As far as the controls go I would say none of them are 'bound' to a table. Some of the controls have 'default values' from a value list. Hope this clarifies a bit.

Thanks for the help,
Aric

[NeoPa]

I'm home now and I have 20 minutes to work on this with you.
I have your database downloaded but I also have questions that would take too long to ask on here.
As you're a gamer, do you use TeamSpeak or Ventrillo?
If so we can make some quick progress.
Please reply ASAP to utilise as much of the remaining 15 mins until 21:00 (20:43 ATM).

[NeoPa]

I'll probably be on again later, so leave me the info & I'll get back to you.
If you want to keep any info private then feel free to PM me those details.
If you choose to leave an e-mail address, in thread or in PM, make sure not to include it in 'naked' (usable) format.
Look forward to hearing from you later.

[AricC]

Yes, I own a vent server. I think we are on some time differences though. I am on US Eastern you? I'll PM you my email and the vent info.

[NeoPa]

I'm on GMT and I live in South London (England).
I don't see your PM yet and I'm back ready so, as soon as you're ready we can go.
I know, being EST you're probably around 17:20 just now (5 hours behind us) so still at work - do you need to do this from home? If so, let me know when that might be.
L8rs -Adrian.

[AricC]

Yes I will need to do this from home. Getting the vent info out to you now.

[NeoPa]

Well, that was fun discussing that and certainly a lot easier to get the points across by talking.
Let us know how you get on with the bound forms and bound controls etc.

[AricC]

Well, that was fun discussing that and certainly a lot easier to get the points across by talking.
Let us know how you get on with the bound forms and bound controls etc.

Yeah I agree vent makes things 100x easier, I was missing quite a "key" point! I'm sure I'll need more help soon. Thanks again Adrian.

Aric