Linking Tables in a Form (1 Viewer)

Diamond-Leopard

Registered User.
Local time
Today, 09:00
Joined
Oct 30, 2018
Messages
26
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
 

isladogs

MVP / VIP
Local time
Today, 09:00
Joined
Jan 14, 2017
Messages
18,186
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
 

Diamond-Leopard

Registered User.
Local time
Today, 09:00
Joined
Oct 30, 2018
Messages
26
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:00
Joined
Oct 29, 2018
Messages
21,357
Hi,


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


Just a thought...
 

AccessBlaster

Registered User.
Local time
Today, 02:00
Joined
May 22, 2010
Messages
5,823
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.

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/forums/showthread.php?t=188663



 

Attachments

  • List_1.jpg
    List_1.jpg
    79.7 KB · Views: 642
  • List_2.jpg
    List_2.jpg
    76.2 KB · Views: 386

isladogs

MVP / VIP
Local time
Today, 09:00
Joined
Jan 14, 2017
Messages
18,186
@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
 

Diamond-Leopard

Registered User.
Local time
Today, 09:00
Joined
Oct 30, 2018
Messages
26
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
 

Users who are viewing this thread

Top Bottom