Dynamically load textbox based on user input

[chris89]

Ok here is a tough one!(I don't even know if this might be possible)

I have uploaded a sample database to show better what I want.

1.User selects from tbl1field1 a value.
2.He enters a value in ValueToTbl1EnteredFormUser

In table3 I have preseted a what goes with what from table1 and table2.
The question is :
Can I somehow find what matches with what , and put each (match) on separate textbox so that the user enters what is needed , and this input be stored in MatchTable??

Thank you in advance and please forgive my ignorance

 

[chris89]

Anyone please?

 

[vbaInet]

chris89, you haven't explained your problem clearly.

But it sounds like you're after the DLookup() function:

http://www.techonthenet.com/access/functions/domain/dlookup.php

 

[chris89]

First of all thank you for your reply!
I upload an updated .accdb for better explanation.
My problem is this:

In table3 I have written the values from table1 that goes with table2.

When a user selects from form1.tbl1field1 a value , I need the MatchTbl1WithTable2 textboxes to have (EACH) a recordsource for each query1.table2ID and a Controlsource to MatchTbl1WithTable2.UserEntries.

As mentioned I don't even know if this might be possible...

 

[vbaInet]

First of all, did you try using the DLookup() function?

 

[chris89]

I don't know exactly where to use Dlookup().
I mean I could use it like a :select,from,where statement . And this in my case would be the recordsource for what?

Thanks again

 

[vbaInet]

Alright, here's some more specific information about this function. Please have a good read:

http://access.mvps.org/access/general/gen0018.htm
http://allenbrowne.com/casu-07.html

 

[chris89]

I don't know where to get with Dlookup() , as from what I understood it is used for query type operations ..
I'm trying to achieve it through a query that gets the needed records according to user's input in form1 and append this to Matchtable .

Please advise me if I am in the correct direction.
(And a picture for my goal!)

 

[vbaInet]

Chris, I have just had a look at your database and it makes absolutely no sense to me. table1, table2, table3, field1, field2, field3 mean nothing. This is why we always advise people to have a good naming convention.

Give me some real examples, put some meaningful data in their and upload it again. Then explain what you're trying to do with a real scenario.

 

[chris89]

Ok I have attached another pic of the procedure and a sample database (form1 and query1 were created only for presentation - they are not functional).

 

[chris89]

And the real scenario is this:
I have to enter for each ProductType ,Products everyday.
I know the ProductType and its value ,and based on the ProductType there are specific Products that must also be entered.
So because of many Products I have to :
1.find what Products there are for each Type
2.select from combobox one Product
3.enter value
4.enter date
5.save&new
(repeat)

So best case scenario is something like this:
1.-let the user select ProductionType
-enter value for ProductionType
2.Create form dynamically as illustrated
3.New Records to ProductionTypeFromForm and ProductsFromForm as illustrated

 

[vbaInet]

Chris, you need to rethink your setup. What is the use of the ProductionTypeFromForm and a ProductsFromForm tables? I don't also understand the relationship between Products, ProductsByType and ProductsType. Five tables just for Products!!!

It seems to me that you are duplicating tables just for data retrieval from the forms which you will then use to update another table eventually.

My advice is you should re-think your approach, properly normalize and forget about creating tables just to accomodate your current needs.

Are your tables normalized?

 

[chris89]

Thank you for your replies
I will try to redesign my database

 

[vbaInet]

Don't start redesigning yet. Think about what information you would like to store, but don't think about how you would store it. Get the tables normalized and the "how" part will fit in nicely. Plan plan and plan again!

A good idea is to post what your normalization question in the Tables section of the forum and someone will be able to help.

In the meantime, here are some links on normalization. They should help give you some pointers:

http://support.microsoft.com/kb/283878
www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx
http://r937.com/relational.html