By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,472 Members | 2,195 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,472 IT Pros & Developers. It's quick & easy.

Search an Access DB

Expert 100+
P: 1,892
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
Dec 18 '06 #1
Share this Question
Share on Google+
39 Replies


Expert 100+
P: 1,892
Please disregard for now I think I have found a decent example that will do the job.

Aric
Dec 18 '06 #2

Expert 100+
P: 1,892
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
Dec 18 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch_Click()
  2.  
  3.    DoCmd.OpenForm "SearchResults", , ,"[ControlName]=" & Me.textboxName
  4.  
  5. End Sub
  6.  
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
Expand|Select|Wrap|Line Numbers
  1. "[ControlName]='" & Me.textboxName & "'"
For Date fields
Expand|Select|Wrap|Line Numbers
  1. "[ControlName]=#" & Me.textboxName & "#"
Mary
Dec 19 '06 #4

Expert 100+
P: 1,892
I'll give that a whirl thanks! I'm sure I'll have some questions.

Thanks :),
Aric
Dec 19 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 19 '06 #6

NeoPa
Expert Mod 15k+
P: 31,186
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.
Expand|Select|Wrap|Line Numbers
  1. "[ControlName]=" & Format(Me.textboxName,'\#m/d/yyyy\#')
See (Literal DateTimes and Their Delimiters (#).) for more info.
Dec 20 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Expand|Select|Wrap|Line Numbers
  1. "[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
Dec 20 '06 #8

Expert 100+
P: 1,892
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
Dec 20 '06 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
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?
Dec 20 '06 #10

Expert 100+
P: 1,892
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 ).
Dec 28 '06 #11

Expert 100+
P: 1,892
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnSearch_Click()
  2. Dim dbRepairs As DAO.Database
  3. Dim qrySearchSerialNumbers As DAO.QueryDef
  4. Dim strSQL As String
  5. Set dbRepairs = CurrentDb
  6. Set qrySearchSerialNumbers = dbRepairs.QueryDefs("qrySearchSerialNumbers")
  7.  
  8. strSQL = "SELECT TRepairs.*, TSerialNumbers.strSerialNumber " & _
  9.             "FROM TRepairs INNER JOIN TSerialNumbers ON TRepairs.intSerialNumberID=TSerialNumbers.intSerialNumberID " & _
  10.             "WHERE TSerialNumbers.strSerialNumber ='" & [Forms]![frmSearchSerialNumber]![txtSerialNumber] & "';"
  11.  
  12. qrySearchSerialNumbers.SQL = strSQL
  13. DoCmd.OpenQuery "qrySearchSerialNumbers"
  14.  
  15. DoCmd.OpenForm "frmSearchResults", , , "[txtSerialNumber]='" & Me.txtSerialNumber.Value & "'" 'etc..........
  16.  
  17.  
  18.  
  19. Set qrySearchSerialNumbers = Nothing
  20. Set dbRepairs = Nothing
  21.  
  22. End Sub
TIA,
Aric

Note: intSeria lNumberID doesn't have extra spaces inbetween, just shows that way.
Dec 30 '06 #12

NeoPa
Expert Mod 15k+
P: 31,186
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.
Dec 30 '06 #13

Expert 100+
P: 1,892
Yes, that's what I want to do populate the form from a query. Do you have any links with examples NeoPa?

Thanks,
Aric
Dec 30 '06 #14

NeoPa
Expert Mod 15k+
P: 31,186
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.
Dec 30 '06 #15

Expert 100+
P: 1,892
No not following kinda new with Access. I'll look around see if I can find some examples.
Dec 31 '06 #16

bartonc
Expert 5K+
P: 6,596
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnSearch_Click()
  2. Dim dbRepairs As DAO.Database
  3. Dim qrySearchSerialNumbers As DAO.QueryDef
  4. Dim strSQL As String
  5. Set dbRepairs = CurrentDb
  6. Set qrySearchSerialNumbers = dbRepairs.QueryDefs("qrySearchSerialNumbers")
  7.  
  8. strSQL = "SELECT TRepairs.*, TSerialNumbers.strSerialNumber " & _
  9.             "FROM TRepairs INNER JOIN TSerialNumbers ON TRepairs.intSerialNumberID=TSerialNumbers.intSerialNumberID " & _
  10.             "WHERE TSerialNumbers.strSerialNumber ='" & [Forms]![frmSearchSerialNumber]![txtSerialNumber] & "';"
  11.  
  12. qrySearchSerialNumbers.SQL = strSQL
  13. DoCmd.OpenQuery "qrySearchSerialNumbers"
  14.  
  15. DoCmd.OpenForm "frmSearchResults", , , "[txtSerialNumber]='" & Me.txtSerialNumber.Value & "'" 'etc..........
  16.  
  17.  
  18.  
  19. Set qrySearchSerialNumbers = Nothing
  20. Set dbRepairs = Nothing
  21.  
  22. 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...
Dec 31 '06 #17

Expert 100+
P: 1,892
Ah-ha. Someone else is getting broken lines in code tags. Maybe not an IE user either...
I use IE for the most part.
Dec 31 '06 #18

NeoPa
Expert Mod 15k+
P: 31,186
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.
Dec 31 '06 #19

Expert 100+
P: 1,892
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
Dec 31 '06 #20

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Jan 1 '07 #21

Expert 100+
P: 1,892
I'll give it a whirl thanks Mary & NeoPa!

Aric
Jan 1 '07 #22

Expert 100+
P: 1,892
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnSearch_Click()
  2. Dim dbRepairs As DAO.Database
  3. Dim qrySearchSerialNumbers As DAO.QueryDef
  4. Dim strSQL As String
  5. Set dbRepairs = CurrentDb
  6. Set qrySearchSerialNumbers = dbRepairs.QueryDefs("qrySearchSerialNumbers")
  7.  
  8. strSQL = "SELECT TRepairs.*, TSerialNumbers.strSerialNumber " & _
  9.             "FROM TRepairs INNER JOIN TSerialNumbers ON TRepairs.intSerialNumberID=TSerialNumbers.intSerialNumberID " & _
  10.             "WHERE TSerialNumbers.strSerialNumber ='" & [Forms]![frmSearchSerialNumber]![txtSerialNumber] & "';"
  11.  
  12. qrySearchSerialNumbers.SQL = strSQL
  13. 'DoCmd.OpenQuery "qrySearchSerialNumbers"
  14. DoCmd.OpenForm "frmSearchResults", , , "[txtSerialNumber]='" & Me.txtSerialNumber.Value & "'"
  15. 'DoCmd.Close acForm, Me.Name
  16. Set qrySearchSerialNumbers = Nothing
  17. Set dbRepairs = Nothing
  18.  
  19. End Sub
  20.  
Jan 2 '07 #23

NeoPa
Expert Mod 15k+
P: 31,186
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnSearch_Click()
  2. ...
  3. strSQL = "SELECT TRepairs.*, TSerialNumbers.strSerialNumber " & _
  4.             "FROM TRepairs INNER JOIN TSerialNumbers ON TRepairs.intSerialNumberID=TSerialNumbers.intSerialNumberID " & _
  5.             "WHERE TSerialNumbers.strSerialNumber ='" & [Forms]![frmSearchSerialNumber]![txtSerialNumber] & "';"
  6. 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.
Expand|Select|Wrap|Line Numbers
  1. "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.
Jan 2 '07 #24

Expert 100+
P: 1,892
Thanks, yes when running alone and given a parameter the query runs as expected.
Jan 2 '07 #25

Expert 100+
P: 1,892
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.
Expand|Select|Wrap|Line Numbers
  1. "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
Jan 2 '07 #26

NeoPa
Expert Mod 15k+
P: 31,186
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?
Jan 2 '07 #27

NeoPa
Expert Mod 15k+
P: 31,186
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.
Jan 2 '07 #28

Expert 100+
P: 1,892
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT TRepairs.*, TSerialNumbers.strSerialNumber
  2. FROM TSerialNumbers INNER JOIN TRepairs ON TSerialNumbers.intSerialNumberID = TRepairs.intSerialNumberID
  3. WHERE (((TSerialNumbers.strSerialNumber)='SOME SERIAL NUMBER GOES HERE'));
  4.  
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.
Jan 2 '07 #29

NeoPa
Expert Mod 15k+
P: 31,186
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.
Jan 2 '07 #30

Expert 100+
P: 1,892
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 :(
Jan 2 '07 #31

NeoPa
Expert Mod 15k+
P: 31,186
Currently, this is my query:
Expand|Select|Wrap|Line Numbers
  1. SELECT TRepairs.*, TSerialNumbers.strSerialNumber
  2. FROM TSerialNumbers INNER JOIN TRepairs ON TSerialNumbers.intSerialNumberID = TRepairs.intSerialNumberID
  3. WHERE (((TSerialNumbers.strSerialNumber)='SOME SERIAL NUMBER GOES HERE'));
  4.  
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.
Jan 2 '07 #32

Expert 100+
P: 1,892
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:
Expand|Select|Wrap|Line Numbers
  1. 'qrySearchSerialNumbers
  2. SELECT TRepairs.*, TSerialNumbers.strSerialNumber
  3. FROM TRepairs INNER JOIN TSerialNumbers ON TRepairs.intSerialNumberID=TSerialNumbers.intSerialNumberID
  4. WHERE TSerialNumbers.strSerialNumber='123';
  5.  
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
Jan 2 '07 #33

NeoPa
Expert Mod 15k+
P: 31,186
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).
Jan 2 '07 #34

NeoPa
Expert Mod 15k+
P: 31,186
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.
Jan 2 '07 #35

Expert 100+
P: 1,892
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.
Jan 2 '07 #36

NeoPa
Expert Mod 15k+
P: 31,186
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.
Jan 2 '07 #37

Expert 100+
P: 1,892
Yes I will need to do this from home. Getting the vent info out to you now.
Jan 2 '07 #38

NeoPa
Expert Mod 15k+
P: 31,186
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.
Jan 3 '07 #39

Expert 100+
P: 1,892
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
Jan 3 '07 #40

Post your reply

Sign in to post your reply or Sign up for a free account.