Consulting

Results 1 to 11 of 11

Thread: Populate unbound text boxes on Access form from another form

  1. #1

    Populate unbound text boxes on Access form from another form

    Using Access that is a part of Office 365, in Windows 10

    I know this has to be simple, but my VBA skills are soooo weak, I just can't make this work.

    I have two Access forms, frmTolerance and frmCalibration.

    Form frmCalibration has 4 unbound text boxes: txtPreTestWt1, txtPreTestWt2, txtPreTestWt3, txtPreTestW4Starting with frmCalibration closed and frm Tolerance open, I want a button on frmTolerance to:
    1. Open frmCalibration
    2. Populate txtPreTestWt1 with 1
    3. Populate txtPreTestWt2 with 5
    4. Populate txtPreTestWt3 with 10
    5. Populate txtPreTestWt4 with 20

    If that can be made to work, I'll modify the code and attach to additional buttons that will populate those text boxes with a different sets of numbers, for example 5, 20, 50, 100

    Thanks so much for your help

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,263
    Location
    The VBA code to do this is fairly straghtforward, however the data will not be retained in any way, so as soon as the Caibration form closes it will be lost.
    The simplest way to do this is to have the VBA code in the Calibration form's On Open event, otherwise you have to refer to the Calibration form in the code.
    In the Form's on open event it would be
    me.txtPreTestWt1 = 1
    me.txtPreTestWt2 = 5
    me.txtPreTestWt3 = 10
    me.txtPreTestWt4 = 20

    For the code in the button it is
    Forms!Calibration.txtPreTestWt1 = 1
    Forms!Calibration.txtPreTestWt2 = 5
    Forms!Calibration.txtPreTestWt3 = 10
    Forms!Calibration.txtPreTestWt4 = 20

  3. #3
    Thanks OBP!
    I edited the code for the button by adding frm to each line, and that worked.
    Forms!frmCalibration.txtPreTestWt1 = 1
    Forms!frmCalibration.txtPreTestWt2 = 5
    Forms!frmCalibration.txtPreTestWt3 = 10
    Forms!frmCalibration.txtPreTestWt4 = 20

    I didn't want to add too much to my first request so it wouldn't be confusing, but I'm taking the liberty to do so now, sure hope you can help me with this next step.

    After those text boxes have those values, I want to duplicate those values in additional text boxes on the frmCalibrationReport. For example, I want the value in txtPreTestWt1 to be copied to txtPreReadAmt1. Next, I want the value in txtPreTestWt2 to be copied to txtPreReadAmt1, etc.

    This failed:
    Private Sub Command11_Click()
    DoCmd.OpenForm "frmCalibrationReport"
    Forms!frmCalibrationReport.txtPreTestWt1 = 1
    Forms!frmCalibrationReport.txtPreTestWt2 = 5
    Forms!frmCalibrationReport.txtPreTestWt3 = 10
    Forms!frmCalibrationReport.txtPreTestWt4 = 20
    Forms!frmCalibrationReport.txtPreReadAmt1 = txtPreReadAmt1
    End Sub

    And this failed:
    Private Sub Command11_Click()
    DoCmd.OpenForm "frmCalibrationReport"
    Forms!frmCalibrationReport.txtPreTestWt1 = 1
    Forms!frmCalibrationReport.txtPreTestWt2 = 5
    Forms!frmCalibrationReport.txtPreTestWt3 = 10
    Forms!frmCalibrationReport.txtPreTestWt4 = 20
    Forms!frmCalibrationReport.txtPreReadAmt1 = Frm!frmCalibrationReport.txtPreReadAmt1
    End Sub

    And this failed:
    Private Sub Command11_Click()
    DoCmd.OpenForm "frmCalibrationReport"
    Forms!frmCalibrationReport.txtPreTestWt1 = 1
    Forms!frmCalibrationReport.txtPreTestWt2 = 5
    Forms!frmCalibrationReport.txtPreTestWt3 = 10
    Forms!frmCalibrationReport.txtPreTestWt4 = 20
    Forms!frmCalibrationReport.txtPreReadAmt1 = Me.txtPreTextWt1
    End Sub

    And this failed:
    Private Sub Command11_Click()
    DoCmd.OpenForm "frmCalibrationReport"
    Forms!frmCalibrationReport.txtPreTestWt1 = 1
    Forms!frmCalibrationReport.txtPreTestWt2 = 5
    Forms!frmCalibrationReport.txtPreTestWt3 = 10
    Forms!frmCalibrationReport.txtPreTestWt4 = 20
    txtPreReadAmt1 = Me.txtPreTextWt1
    End Sub

    Just don't know VBA enough to take this next step.

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,263
    Location
    Probably the simplest way to achiebve this is to not use VBA

    With the Calibration form in design view change the txtPreReadAmt1 field's Control Source [txtPreTextWt1].
    You can do this for each field that you want to transfer, what ever is put in the first field will appear in the second field.

    However the VBA version is almost your second attempt ie Forms!frmCalibrationReport.txtPreReadAmt1 = Frm!frmCalibrationReport.txtPreReadAmt1, the problem is you are using the Amt1, making it equal to itself, instead of the WT1 version. and frm instead of Forms
    Forms!frmCalibrationReport.txtPreReadAmt1 = Forms!frmCalibrationReport.txtPreReadWt1

  5. #5
    Yes, editing the Calibration form would be the simplest way, IF the numbers entered didn't frequently change. However, I am charged with producing reports for the calibration results of approximately 500 weight scales serviced by my son-in-law's company. (I'm just trying help by automating a process that he has done by hand in the past.) And depending upon how a scale is initially configured and calibrated, the set of numbers can be different from scale to scale. As you said, when the form is closed, all the numbers will be erased, but that is exactly what I want to happen since the set of numbers for the next scale will be different.

    And, the calibration process is repeated multiple times a year! With my limited knowledge, all I can think of doing is to create a number of buttons on a form. Each button will have a variation of the VBA discussed here that will open the frmCalibrationReport and insert a particular set of numbers, appropriate to the scale in question. The frmCalibrationReport pulls data for the scale from a table, such as the company owning the scale, the scale's make, model, serial number, etc. etc. What is changing each time is the calibration results

    It's a messy way of doing things, but can't think of any other way to do so. Again OBP, thanks so much for your help!

  6. #6
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,263
    Location
    I would not use VBA for this purpose for one simple reason. To make any changes to the values presented someone needs to be familair with VBA to dos so.
    Values like this should not be "hard coded" in VBA
    They should be stored in the Weight Scale table or in a related table, so that they are automatically displayed when the Weight Scale is chosen from a Drop Down Combo box.
    The results of the calibration itself should also be stored in a seperate table with the calibration date.
    I worked in a Quality Engineering Department for Ford Motor co. So I am quite familiar with data storage for that kind of work.

  7. #7
    And I was so proud of myself of writing my first VBA code.

    Well, Ok OBP, off I go (from scratch) of trying to create what you described as a better way.

  8. #8
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,263
    Location
    You should be proud of yourself, for getting this far, Access and it's VBA is not easy.
    I can assist you with the design of the database if you like, I have designed hundreds of them over the past 30 years.
    A screenshot of your Table relationships would be good, if you have any relationships that is.
    If not a screenshot of your table design(s) would also help.

    Or you could take a copy of the database and then remove the data and post a zipped copy of the blank version on here.

  9. #9
    Wow! Now that's quite an offer! I'm gonna take a crack at what you said first. Then when the window showing my relationships starts to look like a fish net, I'm definitely goanna seek your help. Guess it won't be today, we have a 4 year old great grandchild coming over. I've nicknamed her Tiny Tornado. Very bright, and very interactive, whether you want to engage, or not.

    Again, thanks so much for your help and your offer to help even more.

  10. #10
    OBP.

    Your advice cleared the fog in my mind and led me to redesigning my database, as you advised, by creating a weight scale test values table and then linking the text boxes on the Calibration Report to that table. I'm still using a modified version of the VBA you showed me for special cases where scales with very large capacities are being calibrated, and the testing values are different from the previous times when they were calibrated. So, happy to have a blend from your guidance...so, thanks very much, again!

  11. #11
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,263
    Location

    Good job.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •