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 "SearchResu lts"

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 "SearchResu lts" 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.