Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-18-2018, 09:53 AM   #1
Diamond-Leopard
Newly Registered User
 
Join Date: Oct 2018
Posts: 26
Thanks: 9
Thanked 0 Times in 0 Posts
Diamond-Leopard is on a distinguished road
Linking Tables in a Form

Let me start by saying every time I post here I have done extensive research to the best of my incredibly limited ability and it is only when my ignorance is more superior then my luck do I feel the need to ask the Gurus at Access World. Mainly because I know my question will be hilariously simple for someone who knows what they are doing ( Or what indeed to look for when something doesn't work) Anywho, apology done here is my question.

I have a form and in that form I have a link to my Inventory table which stores all the information on all the products that I sell.

I have an Order Form which is where I place in the item Number and it populates all the information about that item before I enter more information such as the sale date and customer name etc.....

What I would like is when I type into a box (Product Source) I would like the form to look the word I have typed into the box and immediately return the number of times that word has appeared in another table.

Does that make sence?

So For example as I type into a text box I want another text box to pop up with a number, I want it to search a table for how many times that number exists and to produce the number in the text box. But the Order form I am working in is linked to the Orders table which is where all the data goes when I save the record.

In excell I would do something like

Sumif(Index(*Answer Array*,MATCH(*TextboxInput*,*LookupArray*,0)

Any help is greatly appreciated thank you x

Diamond-Leopard is offline   Reply With Quote
Old 11-18-2018, 09:59 AM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,278
Thanks: 105
Thanked 2,491 Times in 2,287 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Linking Tables in a Form

1. Is that to search one field in the other table or all fields .... and does that include memo/long text fields?
2. Why do you want to do this?
3. You do realise your database could slow to a crawl
__________________
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.


Colin
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
The Following User Says Thank You to isladogs For This Useful Post:
Diamond-Leopard (11-18-2018)
Old 11-18-2018, 10:02 AM   #3
Diamond-Leopard
Newly Registered User
 
Join Date: Oct 2018
Posts: 26
Thanks: 9
Thanked 0 Times in 0 Posts
Diamond-Leopard is on a distinguished road
Re: Linking Tables in a Form

1) It is just needed to search one field in the other table
2) I need it to collect the info from the source table to help with processing the order.
3) I did not know that, I don't know how to most efficiently do what I am doing.

Diamond-Leopard is offline   Reply With Quote
Old 11-18-2018, 10:13 AM   #4
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,653
Thanks: 30
Thanked 641 Times in 624 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Linking Tables in a Form

Hi,


Have you tried using DCount()? I wonder if it will work for you.


Just a thought...
__________________
Just my 2 cents...

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.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Diamond-Leopard (11-18-2018)
Old 11-18-2018, 10:15 AM   #5
Diamond-Leopard
Newly Registered User
 
Join Date: Oct 2018
Posts: 26
Thanks: 9
Thanked 0 Times in 0 Posts
Diamond-Leopard is on a distinguished road
Re: Linking Tables in a Form

I am googleing now
Diamond-Leopard is offline   Reply With Quote
Old 11-18-2018, 11:32 AM   #6
AccessBlaster
.
 
Join Date: May 2010
Posts: 1,044
Thanks: 23
Thanked 260 Times in 249 Posts
AccessBlaster has a spectacular aura about AccessBlaster has a spectacular aura about
Re: Linking Tables in a Form

I am struggling to understand why you need a separate popup form to message the user concerning search results. The current results or count can be displayed on the users form.

Quote:
Originally Posted by Diamond-Leopard View Post
So For example as I type into a text box I want another text box to pop up with a number, I want it to search a table for how many times that number exists and to produce the number in the text box. But the Order form I am working in is linked to the Orders table which is where all the data goes when I save the record.
I adapted (pilfered) some code from John Big Booty to aid in a physical inventory database I was working on a few years back. During the searching process the user is updated visually as to the current count.

https://www.access-programmers.co.uk...d.php?t=188663



Attached Images
File Type: jpg List_1.jpg (79.7 KB, 92 views)
File Type: jpg List_2.jpg (76.2 KB, 81 views)
AccessBlaster is offline   Reply With Quote
The Following User Says Thank You to AccessBlaster For This Useful Post:
Diamond-Leopard (11-18-2018)
Old 11-18-2018, 11:48 AM   #7
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,278
Thanks: 105
Thanked 2,491 Times in 2,287 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Linking Tables in a Form

@AB
The OP wants the count to appear (popup) in a text box.
No mention of a popup form

@DL
Counting in one field is perfectly manageable but I recommend you only do this using the after update event.
If you do it after each letter is added (change event) it would slow things down

My real concern was if you had wanted to search every field in the other table.
That would have been like pouring treacle but in slow motion

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


Colin
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
The Following 2 Users Say Thank You to isladogs For This Useful Post:
AccessBlaster (11-18-2018), Diamond-Leopard (11-18-2018)
Old 11-18-2018, 01:38 PM   #8
Diamond-Leopard
Newly Registered User
 
Join Date: Oct 2018
Posts: 26
Thanks: 9
Thanked 0 Times in 0 Posts
Diamond-Leopard is on a distinguished road
Re: Linking Tables in a Form

Thats great! Thank you guys for your input I will attempt to take on all the things mentioned and the reference to the after update event seems a good idea

Cheers guys

Diamond-Leopard is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Linking Multiple Tables to a form ConnorGiles Forms 2 08-26-2014 02:50 AM
Linking several tables on Tabbed Form ? Tjabooo Forms 5 10-07-2013 12:43 PM
Linking tables with form pillaisg General 1 01-04-2008 08:27 AM
Linking Form to Multiple Tables OstranderRS Forms 1 09-30-2007 07:38 PM
Linking a form to multiple tables amb3r Modules & VBA 15 08-09-2006 05:10 PM




All times are GMT -8. The time now is 02:56 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