Pushing Data From one form to another

[zipmaster07]

Probably a pretty common question (and hopefully easy because of it).



I've got two forms call 'em: Form A and Form B

Form A pulls from a table called "lead" which has a column called lead_id (the primary key on that table). When I create a lead (a.k.a. a new entry) with Form A it generates a new lead_id. I also have a button on Form A that opens to Form B. I need the newly generated lead_id from Form A to be listed/carried over to Form B. I have the following syntax as the Control Source on Form B for the lead_id field:

=[Forms]![Form B].[lead_id] = [Forms]![Form A].[lead_id]

however when I run the form, it just shows the following in the text field:

#Type!

Can someone point out what I'm doing wrong?

[June7]

ControlSource would be:
= [Forms]![Form A].[lead_id]

However, this will not save this value to table. That would require VBA code.

Have you considered a form/subform arrangement?

[zipmaster07]

Thanks I'll try that. I'm pulling the data to another form and then I'm going to save it to a table (with some additional data). I'm assuming once I pull the data to the new form I can just reference the name on that form, I shouldn't have to go back to the original to pull the actual data.

[June7]

That is correct.

You are using bound forms?

[zipmaster07]

Yes I am (I'm new to Access and I tried using unbound but was having a difficult time of it).

I'm running into another problem now. I changed the control source and it pulled the correct lead_id but when I try to add the record (by hitting a button I have on the form) it doesn't actually add the lead_id referenced in the text box to the actual table. I'm assuming this is because the text box is bound to the expression I entered not the actual column. The button adds all the other records just fine so I know it's setup, at least, partially correctly. How do I remedy this?

[June7]

Right, the textbox is not bound, calculations don't save, need code.

In the button code, use:

Me!fieldname = Me.textboxname

[zipmaster07]

ahh thank you.

Another simpleton question: I created the button using the wizard so it created an embedded macro. I'll just switch the button to an event procedure. Can I just list all the other fields like you did below to add the rest of the data on the form to the table? Or do I need additional code surrounding that data to tell the system that it needs to actually add these records to the table.

[June7]

If form is bound and controls are bound, no code is needed. Entries to bound controls pass directly to the fields in table. Record is committed when move to another record, form closes, or code to save.

[zipmaster07]

Thanks for your help, I was able to get it working the way I want it to (for now ).

[zipmaster07]

Well that didn't last long, I already ran into something else. I've also got two columns that this form (Form B) pulls from, called contractor_id and contractor_name. I have a combo box that pulls from another table called contractor (which also has it's own id and contractor_name column).

By setting the control source for the combo box as contractor_id and the row source as a query that pulls from the contractor table I'm able to set the contractor_id successfully by selecting a value from the list, but I'd also like to set the contractor_name field. Do I have to have another field on my form with a control source set to contractor_name to set the value in the table or can you somehow (most likely through code) pull the contractor name from the row source of the combo box and manually set the contractor_name on the table. Hope this makes sense.

[June7]

That is duplicating data. No need to save the names to another table. They can always be retrieved by joining tables to view related info. That is a major principle of relational database - don't duplicate data, relate records. That is the reason for the ID fields.

[zipmaster07]

Wow.... all I can say is duh (and have a stupid look on my face). I changed the subform (this was pulling from Form B) on the original form to also pull in the contractor_name from the actual contractor table and it worked perfectly.

Thanks again for your help.

[June7]

Okay, good, but don't want to allow edit of the related contractor info, just display it. Set those textboxes as Locked Yes and TabStop No.

[zipmaster07]

yup will do.

I noticed something else, the text box pulls the lead_id just fine when I have the:

=[Forms]![Form A].[lead_id]

...in the control source, but it always pulls the same #. It's always pulling the lowest seq from the "lead" table. How can I get it to pull the currently displayed # from Form A?

[June7]

That's odd, it should be referring to the record that has focus on Form A.

Form A is in Single, Continuous, or Datasheet view? If Single, are bound controls in the Detail section (grasping at straws)?

If you want to provide project for analysis, I will look at.