Prev: Compile Error: Type Mismatch
Next: TYPE MISMATCH
From: vaa571 on 21 Apr 2010 13:41
Hi there,

I created a form with a search command that searchs my database "Open Order
Table" for a part number and I would like to add another search criteria to
the code below...
Open Orders Table has "Part Number" and "Lot#" field and i would like to be
able to search by the lot# or by the Part Number. If searching by lot# it
woud had to be exact match but in the part number I would like to avoid
typing the entiry entry.
the code below I have to enter the entire part number...

Private Sub Command42_Click()
On Error GoTo Err_Command42_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Open Orders"

stLinkCriteria = "[PART NUMBER]=" & "'" & Me![PART NUMBER] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command42_Click:
Exit Sub

Err_Command42_Click:
MsgBox Err.Description
Resume Exit_Command42_Click

End Sub
From: golfinray on 21 Apr 2010 14:04
See www.allenbrowne.com. Allen has some cascading combo code that might be
the way for you to go for a more extensive search feature.
--
Milton Purdy
ACCESS
State of Arkansas


"vaa571" wrote:

> Hi there,
>
> I created a form with a search command that searchs my database "Open Order
> Table" for a part number and I would like to add another search criteria to
> the code below...
> Open Orders Table has "Part Number" and "Lot#" field and i would like to be
> able to search by the lot# or by the Part Number. If searching by lot# it
> woud had to be exact match but in the part number I would like to avoid
> typing the entiry entry.
> the code below I have to enter the entire part number...
>
> Private Sub Command42_Click()
> On Error GoTo Err_Command42_Click
>
> Dim stDocName As String
> Dim stLinkCriteria As String
>
> stDocName = "Open Orders"
>
> stLinkCriteria = "[PART NUMBER]=" & "'" & Me![PART NUMBER] & "'"
> DoCmd.OpenForm stDocName, , , stLinkCriteria
>
> Exit_Command42_Click:
> Exit Sub
>
> Err_Command42_Click:
> MsgBox Err.Description
> Resume Exit_Command42_Click
>
> End Sub
From: John W. Vinson on 21 Apr 2010 22:28
On Wed, 21 Apr 2010 10:41:01 -0700, vaa571 <vaa571(a)discussions.microsoft.com>
wrote:

>Hi there,
>
>I created a form with a search command that searchs my database "Open Order
>Table" for a part number and I would like to add another search criteria to
>the code below...
>Open Orders Table has "Part Number" and "Lot#" field and i would like to be
>able to search by the lot# or by the Part Number. If searching by lot# it
>woud had to be exact match but in the part number I would like to avoid
>typing the entiry entry.
>the code below I have to enter the entire part number...
>
>Private Sub Command42_Click()
>On Error GoTo Err_Command42_Click
>
> Dim stDocName As String
> Dim stLinkCriteria As String
>
> stDocName = "Open Orders"
>
> stLinkCriteria = "[PART NUMBER]=" & "'" & Me![PART NUMBER] & "'"
> DoCmd.OpenForm stDocName, , , stLinkCriteria
>
>Exit_Command42_Click:
> Exit Sub
>
>Err_Command42_Click:
> MsgBox Err.Description
> Resume Exit_Command42_Click
>
>End Sub

To search by just the beginning of the part number use the LIKE operator with
an * wildcard:

stLinkCriteria = "[Part Number] LIKE """ & Me![Part Number] & "*"""

It's not clear what you want done with the lot number: do you want separate,
independent searches by the two fields, or to search on the part and lot
numbers together, or let Access determine whether the user has entered a
criteirion in one or the other control and run different searches on that
basis?
--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Compile Error: Type Mismatch
Next: TYPE MISMATCH