Dynamic Search to Input Data

[sakioka]

Hello Access World! I'm new to Access and I stumbled upon this place through Google. I started building a database for work (I work at a nursery) and I used John's Dynamic Search code for an Inventory Input Database but I was wondering is it possible to use this code to input data into a table.

So basically I'm creating a database where I can input Item ID, Description(Plant name), Date, Location, Yard, and notes. For the Item ID and Description that is where I used the dynamic search code (as a combo box) so I can easily enter the Item ID and it will search for the Description. I got all this to work except that when I choose my selection it does not save on to the records. The other fields does save just not the dynamic search.

I have tried to figure it out myself but....:banghead::banghead::banghead::banghead:

PLEASE HELP! Thank you!

 

[pbaldy]

Welcome aboard. I moved your thread to a more appropriate forum. Is this what you're after?

http://www.baldyweb.com/Autofill.htm

 

[sakioka]

Welcome aboard. I moved your thread to a more appropriate forum. Is this what you're after?

EDIT:

I figured it out but now how can I make it a dynamic search?

 

[pbaldy]

I'm not clear what you mean. If you use a combo, the user can type in the value as well as select it. If you use limit to list, they can only select an existing value.

 

[sakioka]

I'm not clear what you mean. If you use a combo, the user can type in the value as well as select it. If you use limit to list, they can only select an existing value.

So I have two tables; one with Item ID and Description and one where we can put data in (In the table we have Date Entered, Quantity, Size, Item ID, Description, Location, Yard, Notes). I am trying to make a form where I can search either by plant codes or by plant names and input both into the form just by typing either the Item ID or Description.

I was using this code...

'Populate the string variable with the text entered in the Text Box SearchFor
    vSearchString = SearchFor.Text

'Pass the value contained in the string variable to the hidden text box SrchText,
'that is used as the sear4ch criteria for the Query QRY_SearchAll
    SrchText.Value = vSearchString

'Requery the List Box to show the latest results for the text entered in Text Box SearchFor
    Me.SearchResults.Requery


'Tests for a trailing space and exits the sub routine at this point
'so as to preserve the trailing space, which would be lost if focus was shifted from Text Box SearchFor
    If Len(Me.SrchText) <> 0 And InStr(Len(SrchText), SrchText, " ", vbTextCompare) Then
        Exit Sub
    End If

'Set the focus on the first item in the list box
    Me.SearchResults = Me.SearchResults.ItemData(1)
    Me.SearchResults.SetFocus

'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of  the List Box
    DoCmd.Requery

'Returns the cursor to the the end of the text in Text Box SearchFor
    Me.SearchFor.SetFocus

    If Not IsNull(Len(Me.SearchFor)) Then
        Me.SearchFor.SelStart = Len(Me.SearchFor)
    End If

from John's Dynamic Search Multiple Fields post. But when I use this code it does not input the Item ID or the Description to the table but it inputs all the other information.

Thank you Pbaldy for answering my questions! I really appreciate it.

 

[pbaldy]

I would probably use 2 combos, but if that's working and you just need to bring in another field from the listbox try:

Me.SomeOtherControl = Me.SearchResults.Column(x,1)

where x is the number of the appropriate column.

 

[sakioka]

I would probably use 2 combos, but if that's working and you just need to bring in another field from the listbox try:

Me.SomeOtherControl = Me.SearchResults.Column(x,1)

where x is the number of the appropriate column.

Okay now I have another issue...

I am able to input information from the ItemID and Description into the table through the form now (had to change the Control Source). But now my issue is when I'm trying to enter information for the same plant ItemID/Description it replaces the old information. Is there a way for it not to do that but instead create a new record with the same ItemID and Description?

 

[sakioka]

Description is a "reserved word" in Access. Sounds like you have a 1:1 relationship. You may need a 1:many. Plant on the 1 side and plant details on the many.

I changed the "Description" header to "DescriptionID" and I also changed the relationship to one to many. But still did not do or change anything. Why must access be so difficult!

 

[sakioka]

Post your db.

Here's the database.