Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-07-2017, 04:53 AM   #1
S1LV3RF0X87
Newly Registered User
 
Join Date: Jul 2017
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
S1LV3RF0X87 is on a distinguished road
Dlooup Multiple queries

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.

S1LV3RF0X87 is offline   Reply With Quote
Old 07-07-2017, 04:58 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,367
Thanks: 0
Thanked 742 Times in 727 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Dlooup Multiple queries

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.
Ranman256 is offline   Reply With Quote
Old 07-07-2017, 05:09 AM   #3
S1LV3RF0X87
Newly Registered User
 
Join Date: Jul 2017
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
S1LV3RF0X87 is on a distinguished road
Re: Dlooup Multiple queries

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.

S1LV3RF0X87 is offline   Reply With Quote
Old 07-07-2017, 05:18 AM   #4
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,884
Thanks: 114
Thanked 2,977 Times in 2,710 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Dlooup Multiple queries

So why can't you use a query for your form with filtered data from both tables?
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 07-07-2017, 05:29 AM   #5
S1LV3RF0X87
Newly Registered User
 
Join Date: Jul 2017
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
S1LV3RF0X87 is on a distinguished road
Re: Dlooup Multiple queries

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
S1LV3RF0X87 is offline   Reply With Quote
Old 07-07-2017, 05:33 AM   #6
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,214
Thanks: 89
Thanked 2,021 Times in 1,969 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Dlooup Multiple queries

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?
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 07-07-2017, 05:46 AM   #7
S1LV3RF0X87
Newly Registered User
 
Join Date: Jul 2017
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
S1LV3RF0X87 is on a distinguished road
Re: Dlooup Multiple queries

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 is offline   Reply With Quote
Old 07-07-2017, 06:55 AM   #8
S1LV3RF0X87
Newly Registered User
 
Join Date: Jul 2017
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
S1LV3RF0X87 is on a distinguished road
Re: Dlooup Multiple queries

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 is offline   Reply With Quote
Old 07-07-2017, 07:25 AM   #9
S1LV3RF0X87
Newly Registered User
 
Join Date: Jul 2017
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
S1LV3RF0X87 is on a distinguished road
Re: Dlooup Multiple queries

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.
S1LV3RF0X87 is offline   Reply With Quote
Old 07-07-2017, 07:39 AM   #10
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,884
Thanks: 114
Thanked 2,977 Times in 2,710 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Dlooup Multiple queries

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 07-07-2017, 07:50 AM   #11
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,214
Thanks: 89
Thanked 2,021 Times in 1,969 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Dlooup Multiple queries

Quote:
IMEI's in general are unique.
In database, nothing is generally unique. It's unique or not unique --not partly unique.

Quote:
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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 07-07-2017, 12:39 PM   #12
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,147
Thanks: 3
Thanked 467 Times in 460 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Dlooup Multiple queries

Quote:
In database, nothing is generally unique. It's unique or not unique --not partly unique.
LOL. Bit like half pregnant.

Cronk is offline   Reply With Quote
Reply

Tags
dlookup access forms , vba access 2016

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Queries using multiple queries ryetee Queries 3 02-27-2016 12:07 PM
multiple queries slimjen1 Queries 2 06-10-2014 04:11 AM
Dlooup Problem mohammadagul Forms 8 09-23-2009 07:17 AM
Dlooup grrrrr monvani Tables 17 09-22-2009 06:55 PM
Huge report, multiple queries, multiple tables calford Reports 1 07-09-2006 06:02 PM




All times are GMT -8. The time now is 01:06 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World