MS Access Search 2

[stevedemo]

Hello,

I am a novice to MS Access, I am looking for an alternative to the built in query interface. I would like to pass one or possibly to variables to a query from a form then have the query displayed in a form. I probably didn't explain this well so I have attached a image of what I am trying to do. I have been trying this for a while on my own but gave up and used the built in query interface such as [ Enter First Name ] [ Enter Last Name].

Thanks !!!





-Me-> All I want is the chance to prove money won't make me happy.

 

[MajP]

The second form could be based on a query pulling the values from the first form. Something like:

SELECT datTblPersonnel.strFirstName,
datTblPersonnel.strLastName
FROM datTblPersonnel
WHERE (((datTblPersonnel.strFirstName)=[forms]![frmOne]![strFirstName])) OR (((datTblPersonnel.strLastName)=[forms]![frmOne]![strLastName]));

 

[stevedemo]

Could you attach a DB example ?

MS access it new to me, I primarily use PHP and some VB but I know just enough Access to be dangerous. If you didn't noitce Databases are not my strong point, but I am learning.


-Me-> All I want is the chance to prove money won't make me happy.

 

[stevedemo]

I have attached the sample db I posted photos from.

Thanks


-Me-> All I want is the chance to prove money won't make me happy.

 

[Remou]

How about:

    strWhere = "firstname='" & Replace(Me.firstname, "'", "''") & "' AND lastname='" _
    & Replace(Me.lastname, "'", "''") & "'"
    DoCmd.OpenForm "Table1", , , strWhere

You could use Like, if you do not want an exact match.

 

[stevedemo]

Where would I place the Like command in sql string ?

When I use the built in query I would do something like this: Like "*" & [ Enter First Name ] & "*" but I am not sure how to convert this to a sql string.

That's a lot closer then I was, thanks !!

However when I ran this code snippet I noticed it overwrote the first entry in the table, is there a way I can have it not do that ? ( I am sure it is something I did )


-Me-> All I want is the chance to prove money won't make me happy.

 

[Remou]

It should not overwrite anything. It is a Where string and used with OpenForm it limits the recordset to those records that match the statement.

Like:

strWhere = "firstname Like '*" & Replace(Me.firstname, "'", "''") & "*' AND lastname Like '*" _
    & Replace(Me.lastname, "'", "''") & "*'"

 

[stevedemo]

Thanks Remou,

I needed to make a small adjustment:

   strWhere = "firstname Like '*" & Replace(Me.Text0, "'", "''") & "*' AND lastname Like '*" _
    & Replace(Me.Text2, "'", "''") & "*'"

I had the first form linked to the table this caused the overwrite. My error.

Once again Thanks !!!




-Me-> All I want is the chance to prove money won't make me happy.

 

[stevedemo]

MajP

Could you show me how to convert the code below to a query like your example or point me to faq page that I could learn how to do this myself ?

strWhere = "firstname Like '*" & Replace(Me.Text0, "'", "''") & "*' AND lastname Like '*" _
    & Replace(Me.Text2, "'", "''") & "*'"

-Me? All I want is the chance to prove money won't make me happy.

 

[MajP]

What Remou is suggesting is using the openForm method, which will open a form and you can specify which records to show.
Go to the help file in the vba window and search "openform method"

The OpenForm method carries out the OpenForm action in Visual Basic.

expression.OpenForm(FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)
expression Required. An expression that returns one of the objects in the Applies To list.

FormName Required Variant. A string expression that's the valid name of a form in the current database. If you execute Visual Basic code containing the OpenForm method in a library database, Microsoft Access looks for the form with this name first in the library database, then in the current database.

View Optional AcFormView.

AcFormView can be one of these AcFormView constants.
acDesign
acFormDS
acFormPivotChart
acFormPivotTable
acNormal default. Opens the form in Form view.
acPreview
If you leave this argument blank, the default constant (acNormal) is assumed.


FilterName Optional Variant. A string expression that's the valid name of a query in the current database.

WhereCondition Optional Variant. A string expression that's a valid SQL WHERE clause without the word WHERE.

DataMode Optional AcFormOpenDataMode. The data entry mode for the form. This applies only to forms opened in Form view or Datasheet view

AcFormOpenDataMode can be one of these AcFormOpenDataMode constants.
acFormAdd The user can add new records but can't edit existing records.
acFormEdit The user can edit existing records and add new records.
acFormPropertySettings default
acFormReadOnly The user can only view records.
If you leave this argument blank (the default constant, acFormPropertySettings, is assumed), Microsoft Access opens the form in the data mode set by the form's AllowEdits, AllowDeletions, AllowAdditions, and DataEntry properties.


WindowMode Optional AcWindowMode. The window mode in which the form opens.

AcWindowMode can be one of these AcWindowMode constants.
acDialog The form's Modal and PopUp properties are set to Yes.
acHidden The form is hidden.
acIcon The form opens minimized in the Windows taskbar.
acWindowNormal default The form is in the mode set by its properties
If you leave this argument blank, the default constant
(acWindowNormal) is assumed.


OpenArgs Optional Variant. A string expression. This expression is used to set the form's OpenArgs property. This setting can then be used by code in a form module, such as the Open event procedure. The OpenArgs property can also be referred to in macros and expressions.

For example, suppose that the form you open is a continuous-form list of clients. If you want the focus to move to a specific client record when the form opens, you can specify the client name with the openargs argument, and then use the FindRecord method to move the focus to the record for the client with the specified name.

This argument is available only in Visual Basic.
Remarks
For more information on how the action and its arguments work, see the action topic.

The maximum length of the wherecondition argument is 32,768 characters (unlike the Where Condition action argument in the Macro window, whose maximum length is 256 characters).

You can leave an optional argument blank in the middle of the syntax, but you must include the argument's comma. If you leave a trailing argument blank, don't use a comma following the last argument you specify.

Example
The following example opens the Employees form in Form view and displays only records with King in the LastName field. The displayed records can be edited, and new records can be added.

DoCmd.OpenForm "Employees", , ,"LastName = 'King'"

Probably the most important argument for you is the "where" clause

WhereCondition Optional Variant. A string expression that's a valid SQL WHERE clause without the word WHERE.

not your where clause is a little advanced, because you are building it dynamically. Your final string should look something like

strWhere = "firstname Like '*Mike*' AND lastname Like '*Smith*'"

Remou built this using the code:
strWhere = "firstname Like '*" & Replace(Me.Text0, "'", "''") & "*' AND lastname Like '*" _
& Replace(Me.Text2, "'", "''") & "*'"

In his example the first name was in a field Text0 and the lastName info came from Text2.

He used a replace function in case you asked it to look for O'donnel. In SQL you need to search for O''donnel, by replacing all single parentheses with double.

 

[stevedemo]

Thanks for the reply !!!

The "openForm method" also works Great. I see great potential in what you suggested, once I figured out where to place the code.

Do you know of any user friendly & good reference sites to help me with SQL syntax ?

BTW - This site is awesome, I wish I would have found it months ago !!

-Me? All I want is the chance to prove money won't make me happy.

 

[Remou]

The open form code goes in place of your existing open form code, that is, the click event of your search button on your form1.

 

[ProgramError]

You may also want to take a look at

http://datapigtechnologies.com/flashfiles/searchform.html

Ian Mayor (UK)
Program Error
Always make your words short and sweet. Because you never know when you may have to eat them.