form for table A to search fields from table B and populate into table A

[johnseito]

I have a table called BID with the following fields
bidder, seller, iid, starttime, bidtime, and bidprice

bidder is an id number that is reference to uid of table USER

the USER table have the following fields,
uid, uname, city, and state

I created a BID form for the user to enter data into the BID table.
What I am trying to do but is having trouble with is have the user search for their name that may already be store in the uname field in the user table, and what ever they choose base on their search, the uid of this uname gets store into the bidder field of the bid table. How do I do this, could someone advise me?

After taken care of this step, I still have a second step where the user in the bid form
can search the iid but shows the itemname from the item table, and the value of seller, iid, and starttime from the auction table gets populated into the bid table.

How do I do this, please advise. I hope it makes sense.

iid from the auction table is reference to the itemtype and the following fields exist in the itemtype table.

iid, itemname, description

 

[pbaldy]

Typically a combo box that gets its selections from users but is bound to the appropriate field in the bids table. The wizard should walk you through it.

 

[johnseito]

Thanks pbaldy for your response to my inqueries

I did it for the bidder, pulling uname from the user table.

But I do have a questions, while my list is small of only 15 records, what if there are thousands of records or millions, then I don't think this would be efficient to choose with a combo drop down list, but to search.

How can I do this?

The second problem, I noticed is this. while the combo box works for the one field,
but what if I have three fields I need to pull, and these three fields are pull from the same row.

For example, on the bid table, I have the following fields.

bidder, seller, iid, starttime, bidtime, and bidprice.

and seller, iid, and starttime is reference to the auction table which
has the following fields.

seller, iid, starttime, endtime, minbid, and condition

and iid from the auction table is reference to itemtype and itemtype has the following
fields

iid, itemname, description.

I want my bid form that allow user to select the seller, iid, and starttime in a single row from the auction table and then the value will be inputted onto the bid table which has the following field.

bidder, seller, iid, starttime, bidtime, bidprice.

when the user makes a selection (although I think a search is better because what if there are thousands of records), he should be able to see the seller name and the item name rather than the id.

the iid from the itemtable is reference to the user table and the user table has the following field

uid, uname, city, and state.

While your solution that you mentioned work on one records but how can it work on a group of fields all fall into the same row?

Thanks for your advice.

 

[pbaldy]

A combo can be typed into, presuming the user knows the value to type. There's also this:

http://allenbrowne.com/ser-32.html

Does this help with the second?

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

 

[johnseito]

A combo can be typed into, presuming the user knows the value to type. There's also this:

http://allenbrowne.com/ser-32.html

Does this help with the second?

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

Ok, thanks. I am still checking on the links and see what I can do.

I have a question:

Do you know if access text box or combo box have a feature similar to google, that when I type to search for a word, a drop down list of possible words/ list comes up and the user can then select from the drop down list.


In the table I have Johnson, and I have Johnso,
In the combo box I type just J and Johnson pops up, but not johnso.
I assumed is because I have Johnson in the list before Johnso?

 

[pbaldy]

The combo should go to the point in the list of what you've typed so far. You may want to drop it down in the got focus event so the user can see where they are in the list. You can code something like you describe, which I think is what Allen did.

 

[johnseito]

Ok, thanks. I have looked at Allen's Code and so far I tried it and it has not yield the result I was hoping for.

Maybe I didn't use his codes correctly? I am still analyzing his code and see what I could do.

However I did noticed a problem and that is, the code only runs when I open the form in design view, and then re open it in form view, sort of like resetting it.

I think it should work as when a user is in form view, then they would type in a few character and a sub-list that fits the categories should appear but in this case I type a character or a few character and nothing comes up.

I don't know, maybe I inputted the codes in the wrong section of the VBA editor?

 

[pbaldy]

Maybe I didn't use his codes correctly?

I don't know, maybe I inputted the codes in the wrong section of the VBA editor?

Hard to say without seeing your code.

 

[johnseito]

Ok, I created a access table, with field suburb, state and postcode. I've Attached an image file of the sample data I use as test.

I then create a form called Postcodes, and created a comboBOX. I called this comboBox "Suburb". I left Row Source empty as Allen suggest. I have Bound Column as 1, and Column Count as 3.

After this, I have the Postcodes form open, and in property, I select from the drop down menu Form and in event tab I click the ... three dot button.

VBE opens up, on the left selection I select (General) on the right selection I select (ReloadSuburb) and I enter this code below.

Dim sSuburbStub As String
Const conSuburbMin = 3
Function ReloadSuburb(sSuburb As String)
    Dim sNewStub As String    ' First chars of Suburb.Text

    sNewStub = Nz(Left(sSuburb, conSuburbMin), "")
    ' If first n chars are the same as previously, do nothing.
    If sNewStub <> sSuburbStub Then
        If Len(sNewStub) < conSuburbMin Then
            'Remove the RowSource
            Me.Suburb.RowSource = "SELECT Suburb, State, Postcode FROM Postcodes WHERE (False);"
            sSuburbStub = ""
        Else
            'New RowSource
            Me.Suburb.RowSource = "SELECT Suburb, State, Postcode FROM Postcodes WHERE (Suburb Like """ & _
                sNewStub & "*"") ORDER BY Suburb, State, Postcode;"
            sSuburbStub = sNewStub
        End If
    End If
End Function

Then on the right side I select form, and the left side I select Current and I entered this code:

Private Sub Form_Current()
      Call ReloadSuburb(Nz(Me.Suburb, ""))
End Sub

Then on the right side I select Suburb, and left side select Change and entered this code.

Private Sub Suburb_Change()

 Dim cbo As ComboBox         ' Suburb combo.
    Dim sText As String         ' Text property of combo.

    Set cbo = Me.Suburb
    sText = cbo.Text
    Select Case sText
    Case " "                    ' Remove initial space
        cbo = Null
    Case "MT "                  ' Change "Mt " to "Mount ".
        cbo = "MOUNT "
        cbo.SelStart = 6
        Call ReloadSuburb(sText)
    Case Else                   ' Reload RowSource data.
        Call ReloadSuburb(sText)
    End Select
    Set cbo = Nothing

End Sub

Then in the left side I select Suburb and right side I select AfterUpdate
and I entered this code

Private Sub Suburb_AfterUpdate()
  
    Dim cbo As ComboBox
    Set cbo = Me.Suburb
    If Not IsNull(cbo.Value) Then
        If cbo.Value = cbo.Column(0) Then
            If Len(cbo.Column(1)) > 0 Then
                Me.State = cbo.Column(1)
            End If
            If Len(cbo.Column(2)) > 0 Then
                Me.Postcode = cbo.Column(2)
            End If
        Else
            Me.Postcode = Null
        End If
    End If
    Set cbo = Nothing
End Sub

The Sub Suburb_afterUpdate() code I often get an error message.

Compile error: Method or data member not found on Me.state

Thanks!!!

 

[pbaldy]

The compile error would imply that Me.State is not a control on the form.

 

[johnseito]

The compile error would imply that Me.State is not a control on the form.

Ok thanks so that means I have to create a state and postal code text box or combo box so the error won't show?

And I tried to test it by opening the form in form view and then type in a few characters and it shows no result. Do you know what is wrong?

 

[pbaldy]

Well, that line of code is setting the value of a textbox, so yes, you'd have to create it. Can you post the db here?

 

[johnseito]

do you know where I could post it.

 

[pbaldy]

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[johnseito]

Attached ......

 

[johnseito]

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

So what do you think, do you know why the search list is not working with the code that I provided?

 

[johnseito]

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

How come you ask me to post my database as if you were going to provide suggestion but didn't?






;-)