Subform opens new form that gets its info from the main form. *yuck!*

[danbo]

Based on your description, the click or double click event of that textbox. VBA code goes here, if you didn't know:

http://www.baldyweb.com/FirstVBA.htm

Thanks again for your help. I hate to bother you but do you think you can help me out a bit more?


I tried to put the code in how you have it but with the changes I need as I "think" they are supposed to be and I'm getting an error "Run-Time 424" Object required.
This is the sum of what I am looking at.

The (new) button that is clicked is on the subform and is named text12
when you click on it it opens a form called "Call Details".
On the Call Details form there is a control called txtCID that I want to populate from the main form ""Customers" field called "ID"

What I tried was to put the code you gave me in the text12 event under click. I must have done something wrong because of the error. Can you perhaps write the code I need? This is a huge end of the year project for me that I'm hopeing will score me some points with the boss so it's very much appreciated. Thanks!!

[pbaldy]

Hard to say why you're getting an error without seeing your specific code. The code to copy would look like:

Forms![Call Details].txtCID= Forms!Customers.ID

[danbo]

Tables aren't an issue. With either of those methods you should be able to pull the values from the form controls specified in your picture and put them in the new form. The code wouldn't care what tables were behind either form.

Okay, I have entered the code, (maybe not in the right format) as follows:
Private Sub Text12_Click()
DoCmd.OpenForm
Forms![Call Details].txtcid = Forms!Customers.ID
End Sub
*ERROR* Compile error: Argument not optional shows up and the Private Sub line is hilighted in the VBA debugger.

[pbaldy]

OpenForm requires certain arguments, chief among them the name of the form to be opened. If you check in VBA help you'll see the correct syntax, since I suspect you also want to open the form in data entry mode.

[danbo]

OpenForm requires certain arguments, chief among them the name of the form to be opened. If you check in VBA help you'll see the correct syntax, since I suspect you also want to open the form in data entry mode.

I'll take a look there again and see if anything slaps me in the face this time. I appreciate all of your time and advise!

[danbo]

Hard to say why you're getting an error without seeing your specific code. The code to copy would look like:

Forms![Call Details].txtCID= Forms!Customers.ID

You rock. I got so into what I was doing I didn't think about what I wasn't doing.

Private Sub Text12_Click()
DoCmd.OpenForm "Call Details", acNormal, , , acFormAdd
Forms![Call Details].txtCID = Forms!Customers.ID
End Sub

THIS DID THE TRICK. Now, one last thing if I can. If I want multiple data brought in how would I do that. example. I want to add the following to the same string.
Forms![Call Details].txtphone = Forms!Customers.Home Phone
where would I put this without screwing up what's now working? Thanks again. Im smiling for the first time in DAYS!!!!!!!!!!

[pbaldy]

Glad we got it sorted out. For multiple values, just keep going:

DoCmd.OpenForm "Call Details", acNormal, , , acFormAdd
Forms![Call Details].txtCID = Forms!Customers.ID
Forms![Call Details].txtphone = Forms!Customers.[Home Phone]

Note the brackets that have to be added to the home phone field due to the inadvisable space.

[danbo]

Glad we got it sorted out. For multiple values, just keep going:

DoCmd.OpenForm "Call Details", acNormal, , , acFormAdd
Forms![Call Details].txtCID = Forms!Customers.ID
Forms![Call Details].txtphone = Forms!Customers.[Home Phone]

Note the brackets that have to be added to the home phone field due to the inadvisable space.

Okay, so it worked but unfourtunatly not as well as I would have liked. An interesting thing is happening here. Instead of pulling customer number 514's phone number and displaying it in the phone number field as depicted on the main form Ie. 585-2215, it's putting the customers ID number there. I'm assuming it's pulling from the wrong column on the table. The other issue is that when I open the form from the navigation window it says "Microsoft Access cannot find the referenced form 'Customers'.
Private Sub Text12_Click()
DoCmd.OpenForm "Call Details", acNormal, , , acFormAdd
Forms![Call Details].txtCID = Forms!Customers.ID
Forms![Call Details].txtphone = Forms!Customers.[phone home]
End Sub

Home Phone was changed to phone home to avoid conflict with the subform just incase that was the issue but it didn't seem to do the trick.
Thanks again for your time. I'm gonna have to track you down and buy you a beer!

[pbaldy]

As to putting the ID instead of phone, this would appear to be referring to the ID:

Forms!Customers.ID

should that be a different field? When you open which form from the navigation pane? None of that code should run until you click the textbox, so there's nothing that should be running when either form opens. Are you sure there isn't some other code running?

[danbo]

As to putting the ID instead of phone, this would appear to be referring to the ID:

Forms!Customers.ID

should that be a different field? When you open which form from the navigation pane? None of that code should run until you click the textbox, so there's nothing that should be running when either form opens. Are you sure there isn't some other code running?

So, the first part. the main form, "Customers" has a combo box called Phone Home that is currently displaying the customer's home phone number. the "Call Details" form has a field, "txtphone" that is being populated from the data in "home phone". Instead of actually putting the phone number though, it's placing the customer's ID number there. I've removed the other code to see if that effected it in any way but it had no effect. I think I have to check the properties of the txtphone field to fix this, and will keep working on it. Not sure.

The other part:
I have a navigation form, "Customer Call History" that houses tabs, Customers, refering to the "Customers" form, Open Calls, ect...
If I go to the navigation pane on the left and directly open the "custoemers" form the code works fine. When I try to use it as someone viewing the database would, ie, through the navigation form, "Customer Call History" it doesn't work. I'm assuming that is because "customers" is a subform in on the navigation form. Is that correct?
I guess the form directory "tree" would look something like this.

Database user opens the database and a navigation form is the default form that opens.

Navigation form = Customer Call History Data
on the call history tab is a subform = Navigation subform
that navigation subform shows the "customers" form that the data is pulled from.

Phew.... I think that is it.

Thanks so much for your patience. Again, Im very new and trying my best to describe it in the most understandable way.

[pbaldy]

If it's a combo, you may have to use the Column property to get at the correct column. What's the row source of the combo?

For the form issue, if the form becomes a subform the syntax to refer to it changes:

Forms Refer to Form and Subform properties and controls

[danbo]

If it's a combo, you may have to use the Column property to get at the correct column. What's the row source of the combo?

For the form issue, if the form becomes a subform the syntax to refer to it changes:

Forms Refer to Form and Subform properties and controls

SELECT [Customers].[ID], [Customers].[Home Phone] FROM Customers;
home phone is in column 12 of that table.

And I'm currently checking on the location of the form as it's displayed on the main form.
right now I'm trying
Forms![Call Details].txtCID = Forms![Call details history].navigationsubform.Customers.Form.ID and I get "object doesn't support this property or method. LOL if it's not one thing it's another.

[pbaldy]

Try

Forms![Call Details].txtphone = Forms!Customers.[phone home].Column(1)

the column property is zero based, so 1 is the second column.

[danbo]

Try

Forms![Call Details].txtphone = Forms!Customers.[phone home].Column(1)

the column property is zero based, so 1 is the second column.

Private Sub Text12_Click()
DoCmd.OpenForm "Call Details", acNormal, , , acFormAdd
Forms![Call Details].txtCID = Forms![Call history data]!navigationsubform.Form!ID
Forms![Call Details].txtphone = Forms![Call history data]!navigationsubform.Form![phone home].Column(1)
End Sub

This was the final outcome and is working like a charm. the reason why I was getting the error about the customer field is because I was trying to tell it to look for another subform. Once I removed .Customers.Form! and let it work right it WORKED>

ALSO!!!! THANK YOU!!! the last piece of code you sent did wonders. I have to take a VBA class.

Thanks again for all of your help. If there's anything I can do to repay you let me know. Thanks again and again and again and again!!

[pbaldy]

Happy to help! I do have the "will code for beer" sign, so I'll take you up on that beer when you get to Nevada.