dlookup change

[tinher]

Hi everyone,

I have this code

Dim ID As Long

ID = QuoteID
DoCmd.GoToRecord , , acNewRec
location = DLookup("location", "tblSeller", "QuoteID=" & ID)
UserFK = DLookup("UserFK", "tblSeller", "QuoteID=" & ID)
cmdClose.SetFocus

works really well. It opens a new record using the values in the specific fields.

I would like to open a different form and use values from another form.

I have a Seller table that is really big so I have to be careful how I use it or it will lockup.

I have a form on the sellers table that looks up the seller I need. I sometimes have to use the address to find right seller

What I want to do is find the seller, name, address, phone and copy that to a new record in frmTicket. frmTicket allows me to add what the seller is selling today.

Both forms are connected to my sellers table.

 

[theDBguy]

Hi. We usually recommend against storing redundant information in multiple tables. Are you sure merely storing the ID won’t be enough?

 

[arnelgp]

make the Lookup Form as Pop-up so that it can be used on other forms.
the calling form should pass its "name" to the lookup form so that it will know which form it was called. from the calling form:

Docmd.Openform FormName:="lookupFormName", View:=acNormal, WindowMode:=acDialog, OpenArgs:=Me.Name

now before the lookup form closes, it checks the passed name and set the fields on that form:

Private Sub Form_Unload()
Dim strForm As String
strForm = Nz(Me.Openargs, "")
Select Case strForm
    Case "yourFormName"
        Forms("yourFormName")!SellerName = Me.SellerName
        Forms("yourFormName")!Address = Me.Address
    Case "AnotherCallingFormHere"
        …
        …
End Case
End Sub

 

[tinher]

That looks like what I need.

I am not sure where to put both codes?

I made my lookup form a popup.

After I find the seller I want I have a button that says

"ADD Items to This Seller"

I can make the button open a form with a new record no problem. Do I add the codes to the on load or on close or add them to button?????

 

[tinher]

Hi. We usually recommend against storing redundant information in multiple tables. Are you sure merely storing the ID won’t be enough?

I am no expert so not sure how to explain it. I am not storing the information.

The look up form is needed to find the seller in the over 7000 records. ABC Seller for example

Then I want to open the ticket form and have a new record that has ABC seller in the Seller name field. That will auto fill the address because I have it linked to seller table.

My ticket form works. I have a drop down with all seller names listed. The problem is I may need to lookup the seller by address or phone number since several have the same name. My lookup form lets me search on all 3.

I have tried to add the lookup fields to my ticket form but I cannot get it to work. It will search by name but not address or phone number.

My problem would be fixed if I could.

I just added 3 unbound combo boxes on form connected to sellers table that puts the value in seller name or address or phone number and it auto fills since the form is linked to sellers table.

Maybe I need to change this configuration????

Thanks.