Click or tab to textbox and open popup

[Zydeceltico]

Hi All,

I have a data entry form (frmWeldAssembleInspections) that records data into many fields in table tblWeldAssemble. The user can either tab, enter, or click through the various data entry fields.

I am currently looking at two fields on this form (LengthRequired and LengthActual).

I would like a popup form (frmMeasurement) to "appear" when the user either tabs to one of them or clicks in one of them (LengthRequired or LengthActual).

Then, from the popup form (frmMeasurement) I would like the user to make selections from cboFeet, cboInches, and cboFractions.

The user would then click either a "Save and Exit" which would return the concatenate of the three selections from cboFeet, cboInches, and cboFractions to populate the single data entry field of, for example, LengthRequired. Once this value is returned to the form - (no data will be permanently saved to the record in tblWeldAssemble until all fields on frmWeldAssembleInspection have been filled and a "Save and Exit" on frmWeldAsssembleInspection has been clicked) - the user tabs or clicks into the next data entry control.

How do I accomplish the above?

I don't know how to make the popup form appear.
I don't know how to create a "Save and Exit" Button.
I don't know how to return the concatenated data from the popup form to the data entry field of the main form.

Those are my primary "I don't know how to's..."

I have attached the db. frmWeldAssembleInspections is a mockup of what I would like to see but may not be properly formatted to function for my purpose. The buttons are just dropped on. That form is not connected to anything because I don't know how to do it.

I know just enough about Access to be a danger to myself and others so any guidance is super appreciated.

Shout out to Ridders for guiding me to make lookup tables for Feet, Inches, and Fractions.

Thank You in advance!

Tim

 

[Zydeceltico]

I could also make the labels for "Length Required" and "Length Actual" buttons to open frmMeasurement but I still don't know how to return the data from frmMeasurement once data is entered.

Ridders did show me to use Nz(12*cboFeet) + Nz(cboInches) + Nz(cboFractions) but how do I get that back to the data entry field on frmWeldAssemblyInspections?

 

[Zydeceltico]

OK. I've decided to go with the command button to open frmMeasurement. Got that one down.

Now just need to know how to return the data from frmMeasurement to frmWeldAssembleInspections when I click "Save and Exit" on frmMeasurement.

 

[GinaWhipp]

Hmm...

Forms![frmWeldAssembleInspections]![YourField] = Me.YourFieldOnfrmMeasurement

You will need to do that for each control. Note, if you are concatenating then you will need to do that.

Also, since when adding you can't add NULL or empty, you should probably do this...

Nz(12*cboFeet, 0) + Nz(cboInches, 0) + Nz(cboFractions, 0)

 

[Zydeceltico]

Forms![frmWeldAssembleInspections]![YourField] = Me.YourFieldOnfrmMeasurement

Gina - NOOB question - I get what the above is doing as a callout and pointer but where exactly do I put that? The cbo's on frmMeasurement? If so, where?

Nz(12*cboFeet, 0) + Nz(cboInches, 0) + Nz(cboFractions, 0)

Same NOOB question. Where does this code go?

Thank you SO much for your patience! I feel as if I am on the brink of a "Eureka" moment. lol

Do I click each control and use the Expression Builder?

 

[isladogs]

Just to remind you that in this thread Dimensional data entry, I had said to use:

RequiredLength: Nz(12*cboFeet,0)) + Nz(cboInch,0) + Nz(DLookup("Dec", "tblFractDec", "Fraction = '" & cboFraction & "'"),0)

The Nz functions allow for any blanks in the combo boxes

Anyway, I'm logging off as its 2am here. Hopefully Gina will be still here to assist you further

 

[Zydeceltico]

Thanks Ridders. I do remember that. And sleep well.

Just note for explanation: I'm not converting to fractions anymore. I'm just going to use decimals. Keep it simple. (which I believe changes the second piece of code to Nz(12*cboFeet, 0) + Nz(cboInches, 0) + Nz(cboFractions, 0)

That said - I still don't understand where the code goes exactly.

Attached is the latest - and I think most polished (i.e., everything named appropriately for clarity and no code attached anywhere ATM).

 

[GinaWhipp]

Not 2am here so...

First part you could put it on a Command Button (your Save and Exit) or put it on a double-click event procedure of a Text Box.

The second part I see Colin got to that.

 

[Zydeceltico]

Hi Gina,

I've attached an updated version of the db.

On frmWeldAssembleInspections - on the "Length Required" button (which opens frmMeasurement) I've added this code:

SetTempVar
Name tmpLengthRequired
Expression = [Forms]![frmMeasurement]![cboFeet]+[Forms]![frmMeasurement]![cboInches]+[Forms]![frmMeasurement]![cboFractions]


On the On Load event for frmMeasurement, I've added:

If [DataEntry] = True Then
If Not IsNull([TempVars]![tmpLengthRequired])
Control Name LengthRequired
Property Value
Value =[TempVars]![tmpLengthRequired]
End if

First of all - I'm not at all confident that setting Control Name to LengthRequired is correct.

I don't understand how to pass the concatenated values from cboFeet, cboInches, and cboFractions to tmpLengthRequired.

I'm also struggling with where to place this code to identify a given cbo: Forms![frmWeldAssembleInspections]![LengthRequired] = Me.YourFieldOnfrmMeasurement.

Does Me.YourFieldOnfrmMeasurement become the concatenation code?

I truly apologize for being so thick-headed. I'm trying.

 

[GinaWhipp]

Hmm, first of all I don't work with TempVars so not worrying about that file. All you need to do it put

Forms![frmWeldAssembleInspections]![[B]YourControl[/B]] = Nz(12*cboFeet, 0) + Nz(cboInches, 0) + Nz(cboFractions, 0)

...on the Save and Exit command button on frmMeasurement and it will populate frmWeldAssembleInspections on Close. However, you need to change the YourControl to the control you want populated.

That should answer your question about concatenation.

 

[Zydeceltico]

Hi Gina - and thanks for sticking with me.

I tried your advice.

I've attached a series of screenshots.

The first attached screenshot shows where I placed the expression:
=[Forms]![frmWeldAssembleInspection]![LengthRequired]=Nz(12*[cboFeet],0)+Nz([cboInches],0)+Nz([cboFractions],0)

The second screenshot shows the error I receive when I click the "Save" button after I have made my data selections.

The third screenshot shows where I placed your expression for each of the unbound controls on frmMeasurement (Feet, Inches, Fractions).

I've also attached the db in its current state to this post.

I know the expression is correct. Control name is correct.

I'm still flummoxed. What have I done incorrectly?

Thank You!

Tim

 

[GinaWhipp]

Hmm, that is not where it goes, have a look here...
http://baldyweb.com/FirstVBA.htm

That said, you said a Save and Close button, where is the Close code?

 

[isladogs]

Replace the expression with an event procedure for that button click

Private Sub cmdSaveLR_Click()
    [Forms]![frmWeldAssembleInspection]![LengthRequired]=Nz(12*[cboFeet],0)+Nz([cboInches],0)+Nz([cboFractions],0)
End Sub

Or as Gina said, place the code in the close button for your data entry form

You also need :

Private Sub cmdExit_Click()
    DoCmd.Close
End Sub

You also need to requery the form so the display is updated

However on testing, the code doesn't give the correct outcome
e.g. 5 feet 6 inches and 0.5 should be 66.5 => but display shows 77 with no decimals

Signing off now. Good luck

 

[Zydeceltico]

Thanks Gina and Colin,

That was a major step forward.

Now there is definitely a value being passed from frmMeasurement to frmWeldAssembleInspection.

However:
1) the exit code does not close frmMeasurement....and
2) Colin is correct. Multiple tests return incorrect sums of numbers. Looking at his concatenation I do not why this would happen.

I have all relevant fields set to the Double datatype. Could that have something to do with it?

 

[GinaWhipp]

Hmm, I have some work I MUST get done tonight. Should I finish at a reasonable hour I will look at tonight. If not, it will be tomorrow unless Colin beats me here.

 

[Zydeceltico]

Gina - No worries.

Hey - I did some "debugging." There is a pattern to the calculations.

The attached pic shows a pattern. Ignore the top.

Look at the bottom (when you have a chance).

THe "Null, Null" on the right side is making no selection in "Feet" and no selection in "Inches." The other side (the left side) is entering 0 for Feet and 0 for Inches everytime.

I am very curious to hear your thoughts.

Thank You

Tim

 

[GinaWhipp]

Finally done for the night and really done. I will review tomorrow when I can *think*.

 

[isladogs]

There were lots of small errors
All 3 combos had the ID field as the bound column.
So although it was showing the feet etc, it was using the ID values in the calculations each time.

I could have changed the bound column to 2 in each case.
Instead, I've removed the unnecessary ID field from each table and modified the combos to match. I've also renamed the fraction field as decimal.

Next problem was your 'Field Required' control had a space but the code didn't.
This meant the textbox wasn't displaying the new result.
I've aligned the code...though it would be better to not have saved in controls or field names.

It now correctly calculates the value and updates the textbox.

HTH

 

[MajP]

You may find these functions useful. This is from something I had prior. I would just store decimal inches and using these anywhere in your database including queries you can show the text format.

Public Function DecInchesToString(ByVal DecimalInches As Double, Optional ShowZeros As Boolean = True) As String
   Dim Feet As Long
   Dim Inches As Long
   Dim Remainder As Long
   Dim strFeet As String
   Dim strInches As String
   Dim strFraction As String
      
   Feet = DecimalInches \ 12
   Inches = Int(DecimalInches - (Feet * 12))
   strFraction = GetFractionInches(DecimalInches)
   'This will show 0 feet and 0 inches when set to true
   If ShowZeros Then
     If Not strFraction = "" Then strFraction = "-" & strFraction
     DecInchesToString = Feet & " Feet " & Inches & strFraction & " Inches"
   'This will not show units that are 0 value. 0 Feet 1-1/2 inch would show as simply 1-1/2 inches
   Else
     If Feet = 0 Then
       strFeet = ""
     Else
       strFeet = Feet & " Feet"
     End If
     If Inches <> 0 And strFraction <> "" Then
        strInches = Inches & "-" & strFraction & " Inches"
     ElseIf Inches <> 0 And strFraction = "" Then
        strInches = Inches & " Inches"
     ElseIf Inches = 0 And strFraction <> "" Then
        strInches = strFraction & " Inches"
     End If
     DecInchesToString = Trim(strFeet & " " & strInches)
   End If
End Function


Public Function GetFractionInches(ByVal DecimalInches As Double) As String
  Dim Denominator As String
  Dim Numerator As String
  Dim The64th As Double
  Dim The32nd As Double
  Dim The16th As Double
  Dim The8th As Double
  Dim TheQtr As Double
  Dim TheHalf As Double
  'Code assumes 64th is smallest unit of measure
  If DecimalInches - Int(DecimalInches) = 0 Then
    Exit Function
  End If
  DecimalInches = DecimalInches - Int(DecimalInches)
  The64th = Int(64 * DecimalInches)
  Denominator = "64"
  Numerator = The64th
  The32nd = 32 * DecimalInches
  If The32nd = Int(The32nd) Then
      Denominator = "32"
      Numerator = The32nd
      The16th = 16 * DecimalInches
      If The16th = Int(The16th) Then
        Denominator = "16"
        Numerator = The16th
        The8th = 8 * DecimalInches
        If The8th = Int(The8th) Then
           Denominator = "8"
           Numerator = The8th
           TheQtr = 4 * DecimalInches
           If TheQtr = Int(TheQtr) Then
              Denominator = "4"
              Numerator = TheQtr
              TheHalf = 2 * DecimalInches
              If TheHalf = Int(TheHalf) Then
                Denominator = "2"
                Numerator = TheHalf
              End If
           End If
        End If
      End If
  End If
  GetFractionInches = Numerator & "/" & Denominator
End Function

So you can tell the function if you want to see the zero values or not
0 Feet 11-1/2 Inches
or just
11-1/2 inches

Here is the test

Public Sub TestGetString()
  Dim theDecimal As Double
  theDecimal = 12 * 6
  Debug.Print DecInchesToString(theDecimal)
  Debug.Print DecInchesToString(theDecimal, False)
  theDecimal = 12 * 6 + 11
  Debug.Print DecInchesToString(theDecimal)
  Debug.Print DecInchesToString(theDecimal, False)
  theDecimal = 12 * 6 + 11 + 11 / 32
  Debug.Print DecInchesToString(theDecimal)
  Debug.Print DecInchesToString(theDecimal, False)
  theDecimal = 11 + 11 / 32
  Debug.Print DecInchesToString(theDecimal)
  Debug.Print DecInchesToString(theDecimal, False)
  theDecimal = 12 * 6 + 11 / 32
  Debug.Print DecInchesToString(theDecimal)
  Debug.Print DecInchesToString(theDecimal, False)
End Sub

The results

6 Feet 0 Inches
6 Feet

6 Feet 11 Inches

6 Feet 11-11/32 Inches

0 Feet 11-11/32 Inches
11-11/32 Inches

6 Feet 0-11/32 Inches
6 Feet 11/32 Inches

 

[Zydeceltico]

Colin,

That helps completely! Thank You.....and I've learned a lot as well through the process - especially your taking the time to detail my errors. I learn well from that. I am very appreciative.

Tim