By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,669 Members | 2,546 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,669 IT Pros & Developers. It's quick & easy.

Refering to text box data from a sub form to another form

P: 8
Have a client form with an address sub form. I then open a jobs form and want to display the name and address on the jobs form. No problem with the name which is in the clients from which is the parent of the address form. When I refer to the address form ( which is called Address Subform) I get an error saying the Address form is not found. Looking for a solution.... Thanks
Feb 26 '08 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 446
Hi

Are we to assume that your clients have multiple addresses? Otherwise why would you put their address in a subform ? So each address should be in an 'Addresses' table with it's own AddressID and ClientID fields

If you then open a Job form associated with a client you can locate the client's name (via ClientID or similar) but would have a choice of addresses. You would have to identify one address (by selecting from combo or list box, for instance) and then hold that AddressID on the Job record. The combo box query would be filtered via ClientID so only the current client's addresses appear.

The underlying query for your Job form would include Job joined to Client via ClientID and Job joined to Addresses via AddressID. That way you can select any Client or Address fields you wish to display.

For instance the client's mobile (cellphone) could be from Client but the branch office landline be from Addresses etc.

Is this what you mean?
S7
Feb 27 '08 #2

P: 8
Hey Sierra7,
Found a solution to my problem. Yes I have a clients form with a subform because we can be doing jobs at different address for the client. I have a command button on the client form to open the job form for a new job. I want to job form to show the name and address of the client for reference purposes. The following code is on the click of the open job form button on the client form.

Private Sub cmdCreateForm_Click()
On Error GoTo Err_cmdCreateForm_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim ctl1 As Control
Dim ctl2 As Control
Dim frm1 As Form
Dim strAddress As String


' Assign pointers from main form and subform
Set frm1 = Forms("Clients")
Set ctl1 = frm1.[Addresses Subform]
' Debug.Print ctl1!Address
strAddress = ctl1!Address + " " + ctl1!City + ", " + ctl1!State + " " + ctl1!ZipCode
Debug.Print strAddress

stDocName = "Jobs"

stLinkCriteria = "[ClientID]=" & Me![ClientID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![Jobs]![ClientID].Value = Forms![Clients]![ClientID].Value
Forms![Jobs]![txtJobsClientName].Value = Forms![Clients]![LastName].Value & "," & Forms![Clients]![FirstName].Value
Forms![Jobs]![txtJobsClientAddress].Value = strAddress

Forms![Jobs]![JobType].SetFocus

Exit_cmdCreateForm_Click:
Exit Sub

Err_cmdCreateForm_Click:
MsgBox Err.Description
Resume Exit_cmdCreateForm_Click

End Sub

I have learned that a subform is a control of the main form. I guess its like a two dimensional array. Thanks for the reply.... Take Care...... Walt
Feb 27 '08 #3

Expert 100+
P: 446
Hi Walt

I'm glad you got it working. You must be a programmer if you can get your head around that lot!

I can see that'ill work. They say there is alway more than one way to skin a cat. It's interesing you seem to be working from a CRM point of view which is good; adding a Job to a Client rather than creating a new Job and then identifying the Client.

The bit I'm not keen on is re-writing the whole address onto the new Job. An address_ID would suffice then if an address changes or need correcting, you only have to do it once. But hey! you know all that.

Best of luck

S7
Feb 27 '08 #4

P: 8
Hey Sierra7,
Yes I go back a ways (punched cards in 1964 using EAM equipment to run a wage payroll system). Long time Cobol programmer and then had a consulting firm covert our system to Gupta Sql Windows. Retired in Dec. 2000.
For my sons business project in college we developed a golf course directory of New Jersey courses and created a Cd which we marketed. All that was done in Access 97. Anyway my son now own an Enviromental Testing company in San Diego. I'm currently working on developing a system to automate his reporting system. Having to shake off a lot of rust. About 2 years ago I deceided to throw out all my programming books. Thought I was done and I now try to avoid the computer. But got pulled back in. Actually had to buy some used books on Amazon. Anyway thats a little background. Retired as a Senior Systems Anaylst so have not trouble designing a system. The frustration is trying to figure out how to get access and access vba to do what I want. Thanks to folks like you I can get some help. Take care..... Walt
Feb 27 '08 #5

NeoPa
Expert Mod 15k+
P: 31,275
You may find Referring to Items on a Sub-Form worth a look.
Feb 28 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.