Need a filter search list box 1

[JaredSangco]

Hi all,

Currently, I have a main interface form [frmMainInterface] that has a cmdbutton [cmdEnterPartInformation] to open a Part Entry form [frmPartInformation].

a few things...

1) Once I click on the [cmdEnterPartInformation], is there any way that I can have the [frmPartInformation] display a 'New' record entry instead of displaying all the records? This would strictly be a form where the user would enter a new part.

2) I would also like the option to 'Update' current records, therefore, i was planning on creating a button on the mainInterface form [frmMainInterface] that would be labeled, 'Update Existing part' utilizing the [frmPartInformation] form. NO New part input will be allowed on this form. However, I would like to have a search list box that would contain all PartNames where the user would select and the corresponding record will be displayed.

Any assistance would be appreciated.

 

[TheAceMan1]

How are ya JaredSangco . . .

Have a look at the forms [blue]Data Entry[/blue] property!

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[JaredSangco]

Ace...

Not sure what you are refering to. Would you kindly be more specific, please.

 

[ProgramError]

Press function key F1 and type "data entry property" in the search box.

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

 

[JaredSangco]

Ace and Ian,

Thanks for the tip..

 

[JaredSangco]

All,

Any help with my second question would be appreciated.

 However, I would like to have a search list box that would contain all PartNames where the user would select from and and the corresponding record will be displayed on the form.

I've searched the forms, and this is what I have done, to no avail.

I created a list box [listboxParts] using the wizard populating the ListBox with PartName from [tblParts].

On the forms section,i modified the 'record source' query by adding a WHERE condition at the end like this

WHERE tblParts.PartName =[forms]![FormPartsInformation]![ListBoxParts];

What am I doing wrong?

 

[JaredSangco]

anyone? Do I need to have code behind my [listboxParts]

 

[ProgramError]

If I understand you correctly, you want your user to click on an item in the listbox and the details pertaining to the part showup elsewhere in the form?

If so use the afterupdate event of the listbox to run a query

create a query who criteria for partname is
like "*" & [forms]![FormPartsInformation]![ListBoxParts] & "*"

then either put docmd.openquery "yourqueryname",acnormalview in the afterupdate event or select the query from the dropdown list using the down arrow to the right of the event.

or even update a subforms recordsource. (a little more complex to achieve)

Take a look at the simple search examples on the datapig site. This may help too.

http://datapigtechnologies.com/AccessMain.htm

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

 

[JaredSangco]

Ian,

Do I need to create a new query or can i use the same "record source" query on the form and basically just add

WHERE tblParts.PartName like "*" & [forms]![FormPartsInformation]![ListBoxParts] & "*"

Also, can u assist with this. I'm a bit confused now

then either put docmd.openquery "yourqueryname",acnormalview in the afterupdate event or select the query from the dropdown list using the down arrow to the right of the event.

 

[TheAceMan1]

JaredSangco . . .

You really don't need to modify the forms recordsource. You can perform a [blue]lookup technique[/blue] using a recordsetclone.

Try this . . .

Note: [blue]PK[/blue] stands for primary key.
Note: [blue]you![/blue] substitute proper names in [purple]purple[/purple]:
[ol][li]Revert the forms recordSource [blue]back to its origional[/blue], making sure the PK is included.[/li]
[li]Base the [blue]RowSource[/blue] of the listbox on the forms rowsource, including only the fields you need. Make sure the PK of the [blue]RowSource[/blue] is the first field.[/li]
[li]In the [blue]AfterUpdate[/blue] event of the listbox, copy/paste the following:

[blue]   Dim rst As DAO.Recordset, Cri As String
   
   Set rst = Me.RecordsetClone
   
   If rst.RecordCount > 0 Then
      Cri = "[[purple][b]PKname[/b][/purple]] = [red][b]'[/b][/red]" & [purple][b]ListboxName[/b][/purple].Column(0) & "[red][b]'[/b][/red]"
      rst.FindFirst Cri
      
      If rst.NoMatch Then
         MsgBox "Record Not Found!"
      Else
         Me.Bookmark = rst.Bookmark
      End If
   Else
      MsgBox "No Records!"
   End If
   
   Set rst = Nothing
   
End Sub[/blue]

[/li][/ol]
If your PK is numeric, be sure to remove the two single quotes [red]'[/red] in Cri.

BTW . . . [blue]frmPartInformation[/blue] can serve the dual function of [blue]data entry[/blue] and [blue]update![/blue].

In the [blue]OnClick[/blue] event of [blue]cmdEnterPartInformation[/blue] you'd have:

[blue]   DoCmd.OpenForm "frmPartInformation", , , , [b]acFormAdd[/b][/blue]

And in the button to update:

[blue]   DoCmd.OpenForm "frmPartInformation"[/blue]

When your updating existing records (can't add new), you have to control the [blue]Add New[/blue] line used for new records. The following code in the [blue]On Load[/blue] event of [blue]frmPartInformation[/blue] should do:

[blue]   If Not Me.DataEntry Then Me.AllowAdditions = False[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[JaredSangco]

TheAce... Thanks for being so detailed. Due to numerous meetings today, I'll give it a shot this evening.