Creating a search tool in an Access Page

[jonwolds]

Hi,

Please can someone explain how you create a seach facility in an Access page -say on id number.

Thanks
Jon

 

[Loktar]

Your question is pretty vague, but I'll try to give you an answer that might help.

For something like an ID number, a combo box would be the best way to go I think. It would list all valid ID numbers and wouldn't let a user search for a number that doesn't exist.

Make a form based on a query that has your ID number as part of the data it displays. Then make a combo box on your form and bind it to the field in the query that has your ID number in it. This is done with the Row Source field in the combo box's properties.

Next you will probably want a command button that will open a new form with your filtered data. This can be created using the command button wizard with a few modifications. The wizard will give you some code that looks like this:

Note that the command button is named cmdSearchByID for this example.

Private Sub cmdSearchByID_Click()
On Error GoTo Err_cmdSearchByID_Click

    Dim stDocName As String
    [red]Dim stLinkCriteria As String[/red]

    stDocName = "frmSearchResults"
    [red]stLinkCriteria = "[ID] Like Me![cboID].Value[/red]

    DoCmd.OpenForm stDocName[red], , , stLinkCriteria[/red]

Exit_cmdSearchByID_Click:
    Exit Sub

Err_cmdSearchByID_Click:
    MsgBox Err.Description
    Resume Exit_cmdSearchByID_Click
    
End Sub

The red parts are what you have to add. stLinkCriteria supplies a WHERE part of a SQL query (which is how your data is filtered). This code will open a new form named frmSearchResults. This assumes this form has a control named ID and it will display all records where ID matches the ID selected in the combo box. I named the combo box cboID in the example above.

Let me know if you have any questions. I hope this helps!

 

[jonwolds]

Hi,

Thanks for the info, but can this be done using an Access page to dispay over the web?

Thanks
Jon

 

[Loktar]

That I'm not sure about. My guess is that it would work in a similar way, but I don't have any personal experience with doing Access on the web.

Hopefully someone else will come around who can answer that for you.

 

[jdmd88]

I have an input named "Text6" with a value of "Number" and a button called "sticknum".

Using a query that is sorted (descending) by idnum2, I have used the following:

<SCRIPT language=javascript event=onclick for=sticknum>
if (Text6.value!="Number")
{
try
{ if (MSODSC.DataPages.Count > 0)
if (MSODSC.CurrentSection == null)
do
{
MSODSC.DataPages(0).MoveNext();
}
while (Text6.value()!=document.form1.ticketnum2.value)
else
MSODSC.CurrentSection.DataPage.MoveNext();
}
catch (e)
{
alert ('No Record Found, please try again');
MSODSC.DataPages(0).MoveFirst();
}
}

I know........ I was lazy but this should help. Have in mind that I load the fields from my query onto a form and the newest record in the query is always displayed 1st.

This helps my users search for old id numbers that have not been archieved.

 

[jonwolds]

Thanks for your help.

I will have a go.

Jon