chaning the value of a field from an unrelated form

[mic907]

I have a 'Services' subform that allows a user to enter payment information, but the amount entered is over a specified amount then a field's value in another data is supposed to change (in this case, the 'Contract Status' should change from its default 'Active' to 'Void'). I was able to get an error message to appear upon this occurrence, but I have no idea how to change the value in the other table. here is my code:

Private Sub MaxComp_AfterUpdate()
If Me.MaxComp > 50000 Then
MsgBox "The specified amount is above the $50,000.00 limit. This contract will be flagged and reviewed by HCC to ensure compliance.", vbExclamation, "FMV Limit Exceeded"
'*** [sbfrmContracts]![Contracts]![ContractStatus] = "VOID"
End If

End Sub

***:This is the line I attempted to use to change the field value on the other table

Thank you in advance and happy new year.

 

[boblarson]

I hope you mean that you want to change the value of another control on another FORM which, if bound, will change the value of the field within the table.

To refer to a subform control:

Forms!YourMainFormNameHere.YourSubformContainerNameHere.Form.YourControlNameHere = "Void"

Change these parts to your actual control names:
"YourMainFormNameHere" to the actual main form name
"YourSubformContainerNameHere" to the name of the container that houses your subform on your main form. If you used the wizard to create the subform on the main form, the name of the control will be the same as the subform name, but it could be something else like "ActiveXCtl3."
"YourControNameHere" to the name of the control (like the textbox) that you want to reference or set the value for.
***IMPORTANT*** The Part that says .Form. has got to be there in exactly that position.

So, your code (which I don't have the main form name for so I'm substituting) would be:

Forms!YourMainFormNameHere.sbfrmContracts.Form.ContractStatus = "VOID"

 

[mic907]

wow thanks bob ill try that

 

[mic907]

this is the code im using:

Forms!frmConsultantList.sbfrmContracts.Form.ContractStatus = "VOID"

but i get this error message:

Run-time error '2465'
Application-defined or object-defined error


Not quite sure what the problem is...is there any further info i can tell you that could help?

 

[boblarson]

Does it highlight that line or is it on another line?

If on that line, then make sure that the names of your objects are exactly what you have here. Also, I know the forum sometimes adds a space between letters, but make sure that Contra ctStatus actually says ContractStatus (make sure ContractStatus is the actual textbox name and if it is the same as the field name, you should change it to txtContractStatus) although the name won't cause that error. It's almost that you have an undeclared variable or code that doesn't have a reference set somewhere in your code.

 

[mic907]

well when i open my contracts subform, an error message does appear saying something is wrong with my 'On Current' code. this has never happened before, so i'm not sure what the problem is.

another kink in my database i've been trying to work out is an error with one of my combo boxes auto-populating another field. this function works fine when i open the subform by itself, but when i open the main form, Access requests me to enter parameters for the fields being auto-populated.

could the problems mentioned above be related?

 

[boblarson]

Yes, it could.

What code do you have in the On Current event of your contracts subform?

Also, what is the code in the combo box and if you are updating something in a subform with the combo box then you need to use the correct syntax, but also if you are updating something on the main form from the subform.

 

[mic907]

my Current event code:

Sub Form_Current()

Dim ParentDocName As String

On Error Resume Next
ParentDocName = Me.Parent.Name

If Err <> 0 Then
GoTo Form_Current_Exit
Else
On Error GoTo Form_Current_Err
Me.Parent![Services Subform].Requery
End If

Form_Current_Exit:
Exit Sub

Form_Current_Err:
MsgBox Error$
Resume Form_Current_Exit

End Sub

I believe this was autocreated by the Access wizard when I decided to have a linking form/subform structure.

The code I'm using for the combo-box that autoupdates two other listboxes:

Private Sub PrepareName_AfterUpdate()

Me.lboPhone.Requery
Me.lboFax.Requery

End Sub

the code for the two listboxes being updated:

SELECT [tblNumbers].[AdminID], [tblNames].[AdminName], [tblNumbers].[AdminPhone] FROM tblNames INNER JOIN tblNumbers ON [tblNames].[AdminID]=[tblNumbers].[AdminID] WHERE ((([tblNumbers].[AdminID])=[Forms]![sbfrmContracts]![PrepareName]));

(This code is in the row source; the whole idea with the autoupdating listboxes was to have a admin's respective phone and fax numbers automatically filled, controlled by the selection in the combo box. The way I set this up was I created two tables; tblNames contains the admin names and an ID key assigned to each. The second table, tblNumbers, contains an admin's ID number in one field, and their respective information assigned to each)

 

[boblarson]

First, change this:

...
If Err <> 0 Then
GoTo Form_Current_Exit
Else
On Error GoTo Form_Current_Err
Me.Parent![Services Subform].Requery
End If
...

to this:

...
If Err <> 0 Then
GoTo Form_Current_Exit
Else
On Error GoTo Form_Current_Err
[B]Forms!YourMainFormNameHere.Form.[Services Subform].Requery[/B]
End If
...

And then change

SELECT [tblNumbers].[AdminID], [tblNames].[AdminName], [tblNumbers].[AdminPhone] FROM tblNames INNER JOIN tblNumbers ON [tblNames].[AdminID]=[tblNumbers].[AdminID] WHERE ((([tblNumbers].[AdminID])=[Forms]![sbfrmContracts]![PrepareName]));

to this:

SELECT [tblNumbers].[AdminID], [tblNames].[AdminName], [tblNumbers].[AdminPhone] FROM tblNames INNER JOIN tblNumbers ON [tblNames].[AdminID]=[tblNumbers].[AdminID] WHERE ((([tblNumbers].[AdminID])= [Forms]![B][YOURMAINFORMNAMEHERE][/B].[sbfrmContracts].[B][Form].[/B][PrepareName]));

 

[mic907]

thanks for the suggestions bob, but they didn't seem to do anything for my database. for your first suggestion, I now recieve an error message saying "Access cannot find the field '|' referred to in your expression". For your second suggestion, the same error messages appear, except now one of them states the code you entered (ie: Enter parameter value: Forms!frmConsultantList.sbfrmContracts.Forms....)

 

[boblarson]

Any way you can post a stripped down version of the db here? I would be able to catch what is happening much easier (see my post here for an example of how that helped: http://www.access-programmers.co.uk/forums/showthread.php?t=120330)

 

[mic907]

here you are boblarson; thanks again for all your time and help I really appreciate it.

 

[boblarson]

I'll be getting back to you on this one. I've noticed several problems in there and I will have to wait until I can get home to work on this. I'll get back to you as soon as I can, so hang in there.

 

[boblarson]

Okay, I'll start with this and see if this doesn't get you closer to a working database. There may be other issues too, but I focused on the main ones.

1. The “parameter” that comes up when you open the main form is because you don’t have a subform container named sbfrmContracts. Your subform is named that, but the container on the Main form is not named that (it is named Contracts Subform) and that is the item you need to reference in order for it to work. You can name the container the same thing as your subform name, but it doesn’t have to be thus. But, you must use the container name.
2. You have a second subform that is named sbfrmServices but the container is named “Services Subform”
3. Your second subform is supposed to be a subform under sbfrmContracts, but you have it on the main form. It won’t work to try to set the master/child links to the other subform as it is not a subform on that other subform. You need to change your design. Put the Services Subform on the Contracts Subform and then link by Contract number.

 

[mic907]

really great boblarson...again I appreciate all your time and assistance. I'll give your suggestions a try and get back to you on my results. thanks again for all your help

 

[mic907]

sorry if this is a dumb question, but how do i edit container names??

 

[boblarson]

Go into the main form, click on the outside edge of the subform and that is your container. To select the actual subform within the container (while in the main form) you can click on the gray square in the top left corner of the subform and when you see a black dot there then you have selected the subform and not the container.

 

[mic907]

....nm got it

 

[mic907]

changed the names, but still problems. when i open my main form, it now requests a parameter for "PrepareName"

 

[mic907]

...nm again lol. i re-entered your suggestions from before and it seems to work now...at least no parameter request appears.