relating to other fields..

[Tech]

Hi there.


VBA, how would u identify the forms? I mean, for current form and to go to a particular text box, it would be Me![text field], but would happens if u want to copy a certain value from a certain text box to the parent form to a certain text box there?

 

[Mile-O]

If the fields in a subform then

Me.Parent.txtYourTextBox = Me.txtYourOtherBox

 

[Tech]

Thanks

what i am trying to do is, there is a main form and a sub form.

I want to copy a subforms textbox value into a text box on the MAIN form.

I want this to happen on every record, perhaps using the on current event?

I tried this:

Dim x As String
Dim y As String
Dim finally As String

Me![PurchasesSubForm]![Text21].SetFocus
x = Me![PurchasesSubForm]![Text21].Text

MsgBox (x)

but access produced an error:

"Can't move the focus to the control text21"

This happens when loading the form....any ideas? (I am using msgbox's for testing to see if it picks up the values)

It also doesn't pick up the text! displays 0 all the time

 

[simongallop]

try .Value

 

[Tech]

still says 0

 

[simongallop]

you shouldn't need focus to assign value to variable so try without the first line and try [Forms]! instead of me (not sure about sunforms whether this work as don't use them!)

 

[Tech]

i need to set focus to copy the value of the text box in memory, other wise an error will occur

 

[Tech]

the reason it's showing 0 all the time is because, it takes a few seconds to do the calculation....so, how can I tell it to wait until the calculation is done, then go ahead and take the values?

 

[simongallop]

whats doing the calculation, the VBA code or the textbox?

 

[Tech]

the text box...it's using SQL Statement...so the text box displays:

=[t1]-[t2]

 

[Tech]

right ok:

I made a command button which does the value writting - works!!! WOOHOO!!!

but, how do I tell VBA/ACCESS that, once the values of the text box in the subform have been calculated, hit the button in the main order form?

 

[Tech]

IF I am writing a piece of code in a subform to go and do a button click event, but that button is in the Parent (MAIN) form, what would the command be in order to tell it to go click it?

it is usually commandxxx_click but i can't do that, since it's in a subform and I can't do:

Forms![Order Form]![command_click]

as it expects something after the last ]

any suggestions?

 

[Pat Hartman]

What is the point of copying the value contained in a field from a subform record to the main form? A field only holds a single value and every sub form record that changes places a new value there.

 

[Tech]

yeh i know..it's a long story, but n e way, do u know how to tell the subform (there is a command button i put there) to click a command button on the main, parent, form?

 

[Pat Hartman]

You don't want to run the code from the main form, you want to run it from the sub form. So, you don't need to click a button. Just move the code to some event in the subform.

Me.Parent.ControlName = Me.ControlName

You also don't need to worry about focus since the record that you want to optain the value from has the focus. Think of this as a "push" rather than a "pull". "Pulling" from a subform is a problem since the subform references many records and when you are in the mainform, NONE of the subform records has focus since some control on the main form has focus.

 

[Tech]

ty for making it clearer I appreciate it

the problem is:

I have a button in the main form, this button is suppose to update the price total in the order table because in the subform there is a total calculation and that doesn't update it in the main form, so I put a button on the main form which does it,

in the subform I have a refresh button that refreshes the total in the subform, I was wondering If I could tell that button that once it's done it thing, to go click the main form's button....


but like you are saying it may not be possible, so what I done now is that, put that button's code (from the main form) into the refresh button in the subform, but, how do I tell it to go back to the main form, there is a text box there and to update the value there? I have this:

    Forms![Order Form]![order total].Text = Me![Text21].Text

But when it updates the text box in the order form (main form) it just shows £0.00, which is not correct.

what can be done?

 

[Pat Hartman]

I just noticed that you keep using the .text property. Of course you're having trouble. You want to use the default property of the text box which happens to be .value. Read the help entries on the text and value properties to see when it is appropriate to use .text (which is almost never).

It sounds like the subform field that you are concerned with is in the subform footer so it only occurs once. Rather than moving it to the mainform, why not just adjust the size of the subform so that the footer becomes visible?

 

[Tech]

it still doesn't work give me zero

I am now putting it in the command button in the subform, so now it reads:

Private Sub Command31_Click()

On Error GoTo Err_Command31_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
    
    Dim x As Currency
    Dim y As Currency
    
    Me![Text21].SetFocus
    x = Me![Text21].Text
    
    Forms![Order Form]![Order Total].SetFocus
    y = Forms![Order Form]![Order Total].Value
    
    Forms![Order Form]![Order Total].Value = x

  
   
Exit_Command31_Click:
    Exit Sub

Err_Command31_Click:
    MsgBox Err.Description
    Resume Exit_Command31_Click
    
End Sub

 

[Rich]

Private Sub Command31_Click()

On Error GoTo Err_Command31_Click
DoCmd.RunCommand acCmdSaveRecord
Forms![Order Form]![Order Total]=Me.Text21

Exit_Command31_Click:
Exit Sub

Err_Command31_Click:
MsgBox Err.Description
Resume Exit_Command31_Click

End Sub

 

[Tech]

still doesn't work, gives me £0.00