Dlooup Multiple queries (1 Viewer)

S1LV3RF0X87 · Jul 7, 2017

Hey all, I am having issues trying to pull the make & model of a phone from another table.

I have the following code that when a user inputs the IMEI of a device it goes away and pulls the Make ID(CatergoryID) and the Model ID(ItemID) and then populates those values on my form.

Code:

Private Sub Command181_Click()
    myitem = IMEI

    [CatergoryID] = DLookup("[CatergoryID]", "[Stock]", "[ID]= " & myitem)
    [ItemID] = DLookup("[ItemID]", "[Stock]", "[ID]= " & myitem)

Me.Requery

End Sub

The issue that I have is I have a table for all the makes then another table for all the different devices for that make so in my stock table i just record the ID numbers so the code is working correctly for the most part.

I need to amend it so when it finds the MakeID(CatergoryID) it then looks up that value in the Catergories table to then return the name of that make & vice versa for the the model(ItemsID).

Tables are called;

-Catergories
-Items
-Stock

If anyone can help me out that would great.

Ranman256 · Jul 7, 2017

show all records in a continuous form
then filter based on the picks

Code:

Private Sub Command181_Click()
    myitem = IMEI

    vCatergoryID = DLookup("[CatergoryID]", "[Stock]", "[ID]= " & myitem)
    vItemID = DLookup("[ItemID]", "[Stock]", "[ID]= " & myitem)

    sWhere = "[CatergoryID]=" & vCatagoryID & " and [ItemID]=" & vItemID

  Me.Filter = sWhere
  me.FilterON = true

End Sub

or you could put combo boxes on the form, and user can filter from the combo, thus removing the Dlookup.

S1LV3RF0X87 · Jul 7, 2017

A continuous form is not going to work here.

The stock is recorded on a separate database and I am building a repair database to track faults/repairs.

This is why I need to pull the information from the other table. The form would be based on a different table altogether.

Hope that clears that up.

isladogs · Jul 7, 2017

So why can't you use a query for your form with filtered data from both tables?

S1LV3RF0X87 · Jul 7, 2017

i tried doing a query without much success. i was hoping that the code i have which working to a point that someone could help me go that little bit further with it

jdraw · Jul 7, 2017

Please post a copy of your relationships window.
It sounds to me that you may have a data structure(tables and relationships) issue.

Does IMEI of a device uniquely identify the Make ID(CatergoryID) and the Model ID(ItemID) of an Item?

S1LV3RF0X87 · Jul 7, 2017

In fairness the IMEI is a short text field but IMEI's in general are unique.

As for the relationships i have inherited this repair database which i have had to amend and make changes to in order to be fit for purpose. As far as i can see this database has no such relationships in place and everything works via vba code.

As for the stock database i know that all works of relationships instead and works fine no issues there.

I have had to import another access database in order to link the new tables with this system. Does that mean i am going to have to set up all my relationships again from scratch? Or can i resolve this matter via vba code?

S1LV3RF0X87 · Jul 7, 2017

Okay i have created the relationships from scratch and have managed to get the names showing in a query.

Now trying to display this on a text field in my form but all i get is #Name?

Is there something that i am missing here?

S1LV3RF0X87 · Jul 7, 2017

Okay i have managed to resolve this by inserting a subform into my form which generates the correct make & model name.

However if anyone manages to amend my original vba code to do this without all this messing about please post up as i would love to be able to do it without this messing about.

isladogs · Jul 7, 2017

In post #7 you said IMEI is a short text field
So the code in post #1 should have text delimiters

Try this

Code:

Private Sub Command181_Click()
    myitem = IMEI

    [CatergoryID] = DLookup("[CatergoryID]", "[Stock]", "[ID]='"& myitem & "'")
    [ItemID] = DLookup("[ItemID]", "[Stock]", "[ID]= '" & myitem & "'")

Me.Requery

End Sub

jdraw · Jul 7, 2017

IMEI's in general are unique.

In database, nothing is generally unique. It's unique or not unique --not partly unique.

I have had to import another access database in order to link the new tables with this system.

Import and link are 2 separate things. You do one or the other.

Good luck.

Cronk · Jul 7, 2017

In database, nothing is generally unique. It's unique or not unique --not partly unique.

LOL. Bit like half pregnant.

Dlooup Multiple queries (1 Viewer)

S1LV3RF0X87

Registered User.

Ranman256

Well-known member

S1LV3RF0X87

Registered User.

isladogs

MVP / VIP

S1LV3RF0X87

Registered User.

jdraw

Super Moderator

S1LV3RF0X87

Registered User.

S1LV3RF0X87

Registered User.

S1LV3RF0X87

Registered User.

isladogs

MVP / VIP

jdraw

Super Moderator

Cronk

Registered User.

Similar threads

Users who are viewing this thread