Making a Recordset trouble

[NightWalker]

I have some code that does a conversion of 2 fields a double number and string by multiplying and returning a double number. I have put into the code a debug.print txtPre1 line. The value I am getting here is 0 but it should be the double number from a text box. here is the code for the conversion and where it is being used. The result of the conversion should be assigned to a field called PreReading but I keep getting 0. I hope this is an easy error on my part. Thank you in advance for all your help.

Private Function ConvertUnits(InVal As Double, InUnit As String) As Double
    
    Select Case InUnit
    Case Is = "G"
        ConvertUnits = (InVal * 1000000000)
    Case Is = "M"
        ConvertUnits = (InVal * 1000000)
    Case Else
        ConvertUnits = InVal
    End Select
End Function

If IsNull(Me.SN1.Value) = True Then
    GoTo Continue
Else
    add1 = UCase(Me.SN1)
    rstTestData.AddNew
    rstTestData("FixturePosition") = 1
    rstTestData("SerialNumber") = add1
    Debug.Print txtPre1
    rstTestData("PreReading") = ConvertUnits(txtPre1, cboPreUnit1)
    rstTestData("HighReading") = ConvertUnits(txtHigh1, cboHighUnit1)
    rstTestData("PostReading") = ConvertUnits(txtPost1, cboPreUnit1)
    rstTestData("TestedDate") = addDate
    rstTestData("Technician") = addTech
    rstTestData("TankTestedIn") = addTank
    rstTestData("LoadTested") = addLoad
    rstTestData("MeggerID") = addMeg
    rstTestData("PressureIndID") = addPI
    rstTestData.Update
End If

[Micron]

I see nothing here that assigns a value to txtPre1 or defines what it is.
You DO have Option Explicit turned on and that statement is at the top of your module for this code?
Your code could be simplified, but first things first.

[NightWalker]

txtPre1 is a text box in my form. Yes I have Option Explicit as the second line at the top of my code. Those are just the sections of code that are used in this instance. I was under the understanding that this line "rstTestData("PreReading") = ConvertUnits(txtPre1, cboPreUnit1)" would grab whats in the text box for txtPre1. up above in the code I have txtPre1 as a double and cboPreUnit as a string.

[John_G]

What do you get from the Debug.print txtPre1 ?

If the debug is giving you 0, the error is not in the code you are showing, it is somewhere else; this is just a guess, but if in your form txtPre1 is bound to a table field defined as integer, and you put a value in the text box that is <1, that might be the cause for the 0.

[Micron]

this is just a guess, but if in your form txtPre1 is bound to a table field defined as integer, and you put a value in the text box that is <1, that might be the cause for the 0.

Are you confusing integer with byte? AFAIK, negative numbers are permitted for integers and longs. I agree we need to know more about txtPre1. It could be that an incorrect value is being passed to or returned from the function.

[John_G]

Are you confusing integer with byte? AFAIK, negative numbers are permitted for integers and longs

Oops - I didn't express that very well. I meant that if a small absolute value was entered, for example 0.1 or -0.1, and the underlying table field was type integer, it would result in 0 due to rounding to the integer value. I just did a quick test, and values between -0.5 and 0.5 are rounded to 0.

[NightWalker]

The text box txtPre1 is not associated with any tables it is just an input box for a value. I figured out that I had .....Dim txtPre1 as Double..... I removed this and I started getting an actual value on the debug.print line. Then I figured out that value was still a string. so in the code

rstTestData("PreReading") = ConvertUnits(txtPre1, cboPreUnit1)
rstTestData("HighReading") = ConvertUnits(txtHigh1, cboHighUnit1)
rstTestData("PostReading") = ConvertUnits(txtPost1, cboPreUnit1)

I changed it to

rstTestData("PreReading") = ConvertUnits(CDbl(txtPre1), Me.cboPreUnit1)
rstTestData("HighReading") = ConvertUnits(CDbl(txtHigh1), Me.cboHighUnit1)
rstTestData("PostReading") = ConvertUnits(CDbl(txtPost1), Me.cboPreUnit1)

I also figured out that I needed to put the Me. before the cbo's to get it to pass the value to the convertUnits function.

I am not exactly sure why I had to do all that I had to do to get it to work but It works. If anyone has an explanation I would love to know for the future.

Thank you all for your time.
Walker

[Micron]

Not sure, but I would never Dim a variable using the name of a form control which is what you seem to have. I'd declare a variable and assign Me.txtPre1 to it. I also always use Me before a control name, such as "WHERE tblCust.CustID = " & Me.cmbCust..." lest Access think I'm referring to a field underlying a form record source. If your form is bound and the source has a field of that name, you could pick up that field value from the current record of the form. If that is a new record that defaults to 0, guess what you get? That's also why I do not allow form controls to have the same name as their bound fields. If a wizard creates the form, I go through all the controls and 'fix' that. If you do the above, I don't believe you need to coerce the data type in code as you are if it's already dim'd as a double and properly assigned. What you need to account for with unbound controls is the possibility that a typed in value is of the wrong data type, especially when you are coercing. If a typo resulted in a letter entered along with numbers, your code will error out.

For what it's worth, regarding my comment about the original code, FWIW here's my take. First, GoTo as a means of execution direction other than error handling is frowned upon by the guru's so I avoid it. Altering the test from True to False may not work with whatever else is going on in your code, but I can't see it so take that into consideration. Second, I used your old version since the new example is only partial. What you can do is use With blocks to reduce your typing:

If IsNull(Me.SN1.Value) = False Then
    add1 = UCase(Me.SN1)
With rstTestData
    .AddNew
    !"FixturePosition" = 1
    !"SerialNumber" = add1
    'Debug.Print txtPre1
    !"PreReading" = ConvertUnits(txtPre1, cboPreUnit1)
    !"HighReading" = ConvertUnits(txtHigh1, cboHighUnit1)
    !"PostReading" = ConvertUnits(txtPost1, cboPreUnit1)
    !"TestedDate" = addDate
    !"Technician" = addTech
    !"TankTestedIn" = addTank
    !"LoadTested" = addLoad
    !"MeggerID" = addMeg
    !"PressureIndID" = addPI
    .Update
End With
End If

[NightWalker]

Micron here is all of the code for this form. Any advice like the WITH blocks would be greatly appreciated

Option Compare Database
Option Explicit
Private Sub MegType_AfterUpdate()
On Error GoTo EH
'Sets field values based on MegType selection
Me.MegID = Me.MegType.Column(0)
Me.MegSC = Me.MegType.Column(2)
Me.MegDue = Me.MegType.Column(3)
'Ends code
Exit Sub
'If error occurs
EH:
    MsgBox Err.Number & vbCrLf & Err.Description
    
End Sub
Private Sub PISC_AfterUpdate()
On Error GoTo EH
'Sets field values based on PISC selection
Me.PIID = Me.PISC.Column(0)
Me.PIDue = Me.PISC.Column(3)
'Ends code
Exit Sub
'If error occurs
EH:
    MsgBox Err.Number & vbCrLf & Err.Description
    
End Sub

Private Function ConvertUnits(InVal As Double, InUnit As String) As Double
    
    Select Case InUnit
    Case "G"
        ConvertUnits = (InVal * 1000000000)
    Case "M"
        ConvertUnits = (InVal * 1000000)
    Case Else
        ConvertUnits = InVal
    End Select
End Function

Private Sub btnSubmit_Click()
'On Error GoTo EH
'Checks for data in Tank
If IsNull(Me.Tank.Value) = True Then
    MsgBox "Please enter a tank.", vbOKOnly, "Error"
    Me.Tank.SetFocus
    Exit Sub
End If
'Checks for data in Load
If IsNull(Me.Load.Value) = True Then
    MsgBox "Please enter a load.", vbOKOnly, "Error"
    Me.Load.SetFocus
    Exit Sub
End If
'Checks for data in Date
If IsNull(Me.TestedDate.Value) = True Then
    MsgBox "Please enter a date.", vbOKOnly, "Error"
    Me.TestedDate.SetFocus
    Exit Sub
End If
'Checks for data in MegType
If IsNull(Me.MegType.Value) = True Then
    MsgBox "Please enter a megger type.", vbOKOnly, "Error"
    Me.MegType.SetFocus
    Exit Sub
End If
'Checks for data in PISC
If IsNull(Me.PISC.Value) = True Then
    MsgBox "Please enter a pressure indicator S&C.", vbOKOnly, "Error"
    Me.PISC.SetFocus
    Exit Sub
End If
'Checks for data in Tech
If IsNull(Me.Tech.Value) = True Then
    MsgBox "Please enter a test technician.", vbOKOnly, "Error"
    Me.Tech.SetFocus
    Exit Sub
End If
'Creates variables
Dim add1 As String
Dim add2 As String
Dim add3 As String
Dim add4 As String
Dim add5 As String
Dim add6 As String
Dim add7 As String
Dim add8 As String
Dim add9 As String
Dim addTank As Integer
Dim addLoad As Integer
Dim addDate As Date
Dim addMeg As Integer
Dim addPI As Integer
Dim addTech As String
Dim dbTest As DAO.Database
Dim rstTestData As DAO.Recordset
Dim ctlVar As Control
Dim i As Integer
'Defines variables
addTank = Me.Tank
addLoad = Me.Load
addDate = Me.TestedDate
addMeg = Me.MegID
addPI = Me.PIID
addTech = Me.Tech
'Sets table "tbl_343sTested" as recordset
Set dbTest = CurrentDb
Set rstTestData = dbTest.OpenRecordset("tbl_343sTested")

'----------------------------------------------------------------------------------------
'       Start of writing the recordset
'       Checks each position for a serial number then processes the data for each record
'----------------------------------------------------------------------------------------
'Adds data if data in SN1
'checks for each of 9 records

'Ends code if no data in SN1
If IsNull(Me.SN1.Value) = True Then
    GoTo Continue
Else
    add1 = UCase(Me.SN1)
    rstTestData.AddNew
    rstTestData("FixturePosition") = 1
    rstTestData("SerialNumber") = add1
    Debug.Print CDbl(txtPre1)
    rstTestData("PreReading") = ConvertUnits(CDbl(txtPre1), Me.cboPreUnit1)
    rstTestData("HighReading") = ConvertUnits(CDbl(txtHigh1), Me.cboHighUnit1)
    rstTestData("PostReading") = ConvertUnits(CDbl(txtPost1), Me.cboPreUnit1)
    rstTestData("TestedDate") = addDate
    rstTestData("Technician") = addTech
    rstTestData("TankTestedIn") = addTank
    rstTestData("LoadTested") = addLoad
    rstTestData("MeggerID") = addMeg
    rstTestData("PressureIndID") = addPI
    rstTestData.Update
End If
'Ends code if no data in SN2
If IsNull(Me.SN2.Value) = True Then
    GoTo Continue
'Adds data if data in SN2
Else
    add2 = UCase(Me.SN2)
    rstTestData.AddNew
    rstTestData("FixturePosition") = 2
    rstTestData("SerialNumber") = add2
    rstTestData("PreReading") = ConvertUnits(CDbl(txtPre2), Me.cboPreUnit2)
    rstTestData("HighReading") = ConvertUnits(CDbl(txtHigh2), Me.cboHighUnit2)
    rstTestData("PostReading") = ConvertUnits(CDbl(txtPost2), Me.cboPreUnit2)
    rstTestData("TestedDate") = addDate
    rstTestData("Technician") = addTech
    rstTestData("TankTestedIn") = addTank
    rstTestData("LoadTested") = addLoad
    rstTestData("MeggerID") = addMeg
    rstTestData("PressureIndID") = addPI
    rstTestData.Update
End If
'Ends code if no data in SN3
If IsNull(Me.SN3.Value) = True Then
    GoTo Continue
'Adds data if data in SN3
Else
    add3 = UCase(Me.SN3)
    rstTestData.AddNew
    rstTestData("FixturePosition") = 3
    rstTestData("SerialNumber") = add3
   rstTestData("PreReading") = ConvertUnits(CDbl(txtPre3), Me.cboPreUnit3)
    rstTestData("HighReading") = ConvertUnits(CDbl(txtHigh3), Me.cboHighUnit3)
    rstTestData("PostReading") = ConvertUnits(CDbl(txtPost3), Me.cboPreUnit3)
    rstTestData("TestedDate") = addDate
    rstTestData("Technician") = addTech
    rstTestData("TankTestedIn") = addTank
    rstTestData("LoadTested") = addLoad
    rstTestData("MeggerID") = addMeg
    rstTestData("PressureIndID") = addPI
    rstTestData.Update
End If
'Ends code if no data in SN4
If IsNull(Me.SN4.Value) = True Then
    GoTo Continue
'Adds data if data in SN4
Else
    add4 = UCase(Me.SN4)
    rstTestData.AddNew
    rstTestData("FixturePosition") = 4
    rstTestData("SerialNumber") = add4
    rstTestData("PreReading") = ConvertUnits(CDbl(txtPre4), Me.cboPreUnit4)
    rstTestData("HighReading") = ConvertUnits(CDbl(txtHigh4), Me.cboHighUnit4)
    rstTestData("PostReading") = ConvertUnits(CDbl(txtPost4), Me.cboPreUnit4)
    rstTestData("TestedDate") = addDate
    rstTestData("Technician") = addTech
    rstTestData("TankTestedIn") = addTank
    rstTestData("LoadTested") = addLoad
    rstTestData("MeggerID") = addMeg
    rstTestData("PressureIndID") = addPI
    rstTestData.Update
End If
'Ends code if no data in SN5
If IsNull(Me.SN5.Value) = True Then
    GoTo Continue
'Adds data if data in SN5
Else
    add5 = UCase(Me.SN5)
    rstTestData.AddNew
    rstTestData("FixturePosition") = 5
    rstTestData("SerialNumber") = add5
    rstTestData("PreReading") = ConvertUnits(CDbl(txtPre5), Me.cboPreUnit5)
    rstTestData("HighReading") = ConvertUnits(CDbl(txtHigh5), Me.cboHighUnit5)
    rstTestData("PostReading") = ConvertUnits(CDbl(txtPost5), Me.cboPreUnit5)
    rstTestData("TestedDate") = addDate
    rstTestData("Technician") = addTech
    rstTestData("TankTestedIn") = addTank
    rstTestData("LoadTested") = addLoad
    rstTestData("MeggerID") = addMeg
    rstTestData("PressureIndID") = addPI
    rstTestData.Update
End If
'Ends code if no data in SN6
If IsNull(Me.SN6.Value) = True Then
    GoTo Continue
'Adds data if data in SN6
Else
    add6 = UCase(Me.SN6)
    rstTestData.AddNew
    rstTestData("FixturePosition") = 6
    rstTestData("SerialNumber") = add6
    rstTestData("PreReading") = ConvertUnits(CDbl(txtPre6), Me.cboPreUnit6)
    rstTestData("HighReading") = ConvertUnits(CDbl(txtHigh6), Me.cboHighUnit6)
    rstTestData("PostReading") = ConvertUnits(CDbl(txtPost6), Me.cboPreUnit6)
    rstTestData("TestedDate") = addDate
    rstTestData("Technician") = addTech
    rstTestData("TankTestedIn") = addTank
    rstTestData("LoadTested") = addLoad
    rstTestData("MeggerID") = addMeg
    rstTestData("PressureIndID") = addPI
    rstTestData.Update
End If
'Ends code if no data in SN7
If IsNull(Me.SN7.Value) = True Then
    GoTo Continue
'Adds data if data in SN7
Else
    add7 = UCase(Me.SN7)
    rstTestData.AddNew
    rstTestData("FixturePosition") = 7
    rstTestData("SerialNumber") = add7
    rstTestData("PreReading") = ConvertUnits(CDbl(txtPre7), Me.cboPreUnit7)
    rstTestData("HighReading") = ConvertUnits(CDbl(txtHigh7), Me.cboHighUnit7)
    rstTestData("PostReading") = ConvertUnits(CDbl(txtPost7), Me.cboPreUnit7)
    rstTestData("TestedDate") = addDate
    rstTestData("Technician") = addTech
    rstTestData("TankTestedIn") = addTank
    rstTestData("LoadTested") = addLoad
    rstTestData("MeggerID") = addMeg
    rstTestData("PressureIndID") = addPI
    rstTestData.Update
End If
'Ends code if no data in SN8
If IsNull(Me.SN8.Value) = True Then
    GoTo Continue
'Adds data if data in SN8
Else
    add8 = UCase(Me.SN8)
    rstTestData.AddNew
    rstTestData("FixturePosition") = 8
    rstTestData("SerialNumber") = add8
    rstTestData("PreReading") = ConvertUnits(CDbl(txtPre8), Me.cboPreUnit8)
    rstTestData("HighReading") = ConvertUnits(CDbl(txtHigh8), Me.cboHighUnit8)
    rstTestData("PostReading") = ConvertUnits(CDbl(txtPost8), Me.cboPreUnit8)
    rstTestData("TestedDate") = addDate
    rstTestData("Technician") = addTech
    rstTestData("TankTestedIn") = addTank
    rstTestData("LoadTested") = addLoad
    rstTestData("MeggerID") = addMeg
    rstTestData("PressureIndID") = addPI
    rstTestData.Update
End If
'Ends code if no data in SN9
If IsNull(Me.SN9.Value) = True Then
    GoTo Continue
'Adds data if data in SN9
Else
    add9 = UCase(Me.SN9)
    rstTestData.AddNew
    rstTestData("FixturePosition") = 9
    rstTestData("SerialNumber") = add9
    rstTestData("PreReading") = ConvertUnits(CDbl(txtPre9), Me.cboPreUnit9)
    rstTestData("HighReading") = ConvertUnits(CDbl(txtHigh9), Me.cboHighUnit9)
    rstTestData("PostReading") = ConvertUnits(CDbl(txtPost9), Me.cboPreUnit9)
    rstTestData("TestedDate") = addDate
    rstTestData("Technician") = addTech
    rstTestData("TankTestedIn") = addTank
    rstTestData("LoadTested") = addLoad
    rstTestData("MeggerID") = addMeg
    rstTestData("PressureIndID") = addPI
    rstTestData.Update
End If
Continue:
'Notifies of successful data entry
MsgBox "Data has successfully been entered.", vbOKOnly, "Success"
'----------------------------------------------------------------------------------------
'           Sets print to automatic or Preview(for developement)
'----------------------------------------------------------------------------------------
'DoCmd.OpenReport "rpt_343DataSheet", acViewNormal
'DoCmd.Close acReport, "rpt_343DataSheet"
DoCmd.OpenReport "rpt_343DataSheet", acViewReport
 
Print_Continue:
'Ends code
Exit Sub
'If error occurs
EH:
    If Err.Number = 2501 Then
        GoTo Print_Continue
    Else
        MsgBox Err.Number & vbCrLf & Err.Description
    End If
End Sub

[aytee111]

You can do this in a loop. Here is a sample:

For x=1 to 9
 If Not IsNull(Me("SN" & x).Value) Then 
  with rstTestData
   .AddNew
   .FixturePosition=x
   .SerialNumber=UCase(Me("SN" & x)
   .PreReading=ConvertUnits(CDbl(Me("txtPre" & x)), Me("cboPreUnit" & x)

[Micron]

That's a lot of code for what you appear to be doing. I have to wonder why you're not just running an update query that gets all of these form control values and updates your table instead of creating a recordset of that table and working with that. It would be more elegant.

Looping would be one way. I've never seen A Me reference that looks like that, so I don't know about that one. I'll have to try it sometime I guess. If it doesn't you could use the tag property for a set of controls you want to loop through, which is what I'd do to validate required controls are not null. Since the check for null and the controls you want to use for your recordset stuff are not the same ones, you could do the following for them as well, just using a different tag.

You add a value to the control tag property in the property sheet, such as reqd (no quotes IIRC). Then you loop through the form controls looking for that tag. For the skeptics, a hundred controls can be checked in a second or two. What you have to handle is controls that don't have a tag property (I think only one type, can't remember which) with an error trap (Resume Next) OR if you only want to do this for text boxes, it's easy enough to only check them.

For each ctl in Me.Controls
  If ctl.Type = acTextbox Then
    If ctl.Tag = "reqd" Then 
      If IsNull(ctl) or ctl = "" Then
        'do stuff
        exit sub (or function)
      End If
    End If
  End If
Next

[NightWalker]

Micron,

I am self taught in Access and a lot of my code is very elementary so to speak to get things done. I am still pretty new at all this because it is a side job at my work in addition to my actual job. I don't get to work on it all the time but I am getting more knowledge about access and VBA programming thanks to you all here.

I have to wonder why you're not just running an update query that gets all of these form control values and updates your table instead of creating a recordset of that table and working with that.

I am doing this because those 9 records have to be printed out on a report immediately after the test is completed. I am not sure if this makes any difference but it was the only way I knew how to accomplish what I was trying to do. The biggest trouble I have with access is I have no real programming background. I am not sure where I should go to learn those basics but it would probably be very beneficial. Thank you for all your shares of your knowledge.

[Micron]

An update or append query that has Forms!frmYourForm.YourControl as criteria or update values would update your table. So maybe a user enters values & clicks a button to open the report. After validating form entries, query is run and the report is opened. The report can also be filtered to a value that comes from the form if the table has multiple records. That's what I meant by simplicity. Learning vba is a great idea, and for me, it's fun to be able to do things with it that are not possible any other way. However, it should not be your first go-to. Years or months, maybe only weeks later, when it needs to be modified you will be scratching your head as you weed through code trying to find the offending part. Notes help, but simplicity is trump.

[aytee111]

simplicity is trump

Is the converse also true?

[John_G]

simplicity is trump.

With or without a capital "T"?