Hello all,
I'm a very novice user of Access so please be gentle...
I have a main form with several subforms within it, so users can search for client profile, workdate...etc.
What I need now is to somehow copy the searched(current) values from the subform and put them in the main form's text/combo boxes.
I've been looking every where to make this work and came to understand that "command button" with code in it is the best way to go for this.
Can someone please help me with this one?
My form structure is:
Main form's name: [MAIN-ENTER]
subforms: [client-query], [workdate-query], [address-query]

subforms are "autolookup queries". and they are not "related" to eachother..., so access says...
PLEASE HELP!

What I would sugggest is to add the PK from the main form to your subforms. Access may try to sync the forms, so you may have to manually prevent that if that doesn't work for you.

Since the records are already in the sub form, why would you want to duplicate the information in the main form? I am assuming they are bound text fields.

How are the values in the subforms being "searched"? I think that is the answer to your issue here. For example, if the user selects a value from a combo box, and then results are shown in the subform, you may be able to capture that value and paste the results to the combo box. If the sub form shows multiple records and they can choose one, then I would make the sub form a continuous form, put a button on the details section, and the code on the button's On Click event would be:

Dim 'declare variables for fields to be captured.

sVar1 = Me.? 'take values to each variable.

[Forms]![MAIN-ENTER].[txtFieldOne] = sVar1
[Forms]![MAIN-ENTER].[txtFieldTwo] = sVar2

This will post the values from the record to the main form. There will be as many buttons as there are records in the sub form.

A better way to do this would be to take the PK of the record in the main form and post it to a junction table with the table supplying data to the subforms. Without more info on the structure of the database, it would be hard to help here. Anyway, continuous forms for the sub forms to make each record selectable seems the way to go.

Did that help?


mmcdonal

mmcdonal,
thank you for your reply! Here are some answers to your questions to my best knowledge...:


I would love to do that but, I do not know what PK is... or how to add it...

As far as the functionality of the form goes..., it's working just fine. And as you've just mentioned, I do not need the information searched twice since subform works fine. PROBLEM is, 1) this form needs to be printed on a government issued pre-printed form (matching the exact column location) with current record/data. 2) when users go back to check the record(for example, last week's entered data), I can't make it to see which name was searched for that certain record... and yes, I think it's because it is "bound field". So far, users have to search in subform and manually type the results in the main form's matching fields.

Subforms are "Auto lookup Queries" that was created with seperate tables. For example, there is a main table called "MAIN" and there is a table called "CLIENT". I created query that searches for client's information from this client table, looking for ssn & address & phone...etc. On the "CLIENT-QUERY" subform, users choose name of client with combo box, then results are shown in text boxes. I WOULD LOVE TO CAPTURE THE VALUES AND PASTE THE RESULTS, BUT I DO NOT KNOW HOW. This is the whole question here...
I guess this would work, as long as I can MAKE IT work..., but I can't... This is the code I put in to my code after i changed it:
[forms]![main-enter].[clientName] = svar1
[forms]![main-enter].[clientPhone] = svar2
didn't work... sorry... I know I'm missing something. But what?

Can you please explain what PK is?? sorry, I'm novice.
And yes! any help is a big help for me now. This kinda gave me an idea of which direction I'm supposed to look.
Thank you. Thank you for your future help!

pari

Sorry, the PK is the Primary Key, or unique identifier for the record. Preferably nonmeaningful data, like a sequential integer (autonumber). FK refers to Foreign Key, which is a look up column that refers to the PK in a foreign table.

As to the second issue I see here, let's assume you have some bound text fields on the main form called "txtFirst" and "txtLast" and you want the client's first name and last name put into those respectively, and stored in your record source table.

Let's assume that the first name and last name are on the sub form as fields "FirstName" and "LastName".

Let's assume that you are using a continuous sub form, with a button in the details section (one button will then show up for each record.)

So the On Click event of the button on the Sub Form's detail section will have this code:

Dim sLast As String
Dim sFirst As String

sLast = Me.LastName
sFirst = Me.FirstName

[Forms]![MAIN-ENTER].[txtFirst] = sFirst
[Forms]![MAIN-ENTER].[txtLast] = sLast

This will take the first name and last name from the sub form and put it in the FirstName and LastName bound text fields in the main form.

We didn't discuss this, but you will want to use bound fields in the main form in order to store these values for subsequent reports. This is why I suggested the proper underlying table structure instead.

Did that help?




mmcdonal

mmcdonal,

First of all, did I mention I thank you? If not, THANK YOU VERY MUCH.
It is now trying to work! just have little ticks that's gotta change -- which I think I can handle... maybe.

Got it. Primary and Foreign keys, yes, I got it. And it is being used. but PK for main table and PK for the subs DO NOT, and in some cases, CAN NOT match at this time... Because some stupid guy made clients name as PK in some tables.

WORKED WITH MY TESTING DATABASE LIKE A CHARM! Going to actually test in real one. I'll get back to you on that.

Yes. Done. thank you.

can i say...
HELL YEA!! THANK YOU VERY MUCH!!
i'd pay you now if I can. lol.

Just to clarify, the PK and FK should match, but the PK in the child table should not match the PK in the parent table.

For example: If you have this structure and data:

tblCustomer
PK First Last State (FK)
1 John Smith MD (2)
2 John Doe DE (1)

tblState
PK State
1 DE
2 MD

The tblCustomer table has its own PK for customer records, and the tblState has its own PK for State names, but the FK in tblCustomer does not need to match the PK in tblCustomer, etc. As long as tblCustomer knows which reocrd to associate with in tblState through the FK, their PK's can have any value.

I am not sure if this is what you were thinking.

Anyway, glad to help out.


mmcdonal

mmcdonal,

changed the code little bit from the testing one, and works like a charm! You are now listed as my "Master"...

Thank you, Thank you.

Now, another question...,
I need to make the form able to add a field to a certain table...
For example, if user need to add a brand new field to a existing table, they click on something(command button?) and then, they answer some questions(field properties), then it adds that field to the table.
IS THIS EVEN POSSIBLE?

Now why would you want to do that? You can't allow users to redesing the data structure on the fly.

mmcdonal

Master,
You're right... Right after I posted last one, more I thought about it, less sense it made..
Here is why I even asked...,
I've already made a form for this one company(designed to fit a government issued "pre-printed"form), but government is keep changing the format of the form... adding or changing the fields.
so, the users asked me if there is a way of changing things by themselves if needed -- which, I thought, would be nice feature at the time... Well, that's that.
Then, can you answer this one for me?
Is it possible to make a form to open without the MS Access program window? i've tried the startup option thing and it takes care of everything exept the MS access window. I want ONLY the form to be opened. I guess I could make "data access" page, but is it possible in any other way?

Microsoft makes a product specifically for this application called "InfoPath."
It is an XML based application that receives data like a stand alone form, and then passes it to the DBMS of your choice (in this case, Access).
Another option is to program a short-cut to open Access and minimize it, and use a pop-up modal form for data submission.

mmcdonal