Making a Recordset trouble

[Micron]

LOL. Remains to be seen?


Of course I had euchre in mind.
(still laughing)

[ssanfu]

<snip> Looping would be one way. I've never seen A Me reference that looks like that, so I don't know about that one.<snip>

See http://access.mvps.org/access/forms/frm0003.htm



@NightWalker

Haven't tested this, but it should work

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) = 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) = 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) = 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) = 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) = 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) = True Then
        MsgBox "Please enter a test technician.", vbOKOnly, "Error"
        Me.Tech.SetFocus
        Exit Sub
    End If

    'declares variables
    Dim dbTest As DAO.Database
    Dim rstTestData As DAO.Recordset

    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 i As Integer
    Dim bCompleted As Boolean  'looping completed?

    '    Dim ctlVar As Control   '??? appears to be unused

    'Defines variables
    addTank = Me.Tank
    addLoad = Me.Load
    addDate = Me.TestedDate
    addMeg = Me.MegID
    addPI = Me.PIID
    addTech = Me.Tech
    bCompleted = True

    'Open 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
    '----------------------------------------------------------------------------------------

    'checks for each of 9 records
    For x = 1 To 9     'Adds data if data in SNx
        If Len(Trim((Me("SN" & x)))) > 0 Then
            With rstTestData
                .AddNew
                !FixturePosition = x
                !SerialNumber = UCase(Me("SN" & x))
                '  Debug.Print CDbl(txtPre1)
                !PreReading = ConvertUnits(CDbl(Me("txtPre" & x)), Me("cboPreUnit" & x))
                !HighReading = ConvertUnits(CDbl(Me("txtHigh" & x)), Me("cboHighUnit" & x))
                !PostReading = ConvertUnits(CDbl(Me("txtPost" & x)), Me("cboPreUnit" & x))
                !TestedDate = addDate
                !Technician = addTech
                !TankTestedIn = addTank
                !LoadTested = addLoad
                !MeggerID = addMeg
                !PressureIndID = addPI
                .Update
            End With
        Else
            ' if Me("SN" & x) is Null or empty
            bCompleted = False
            Exit For
        End If
    Next

Continue:

    If bCompleted Then
        '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
    Else
        MsgBox "Possible problem with " & Me("SN" & x), vbOKOnly, "Oops!"
    End If

Exit_Here:
    'clean up
    rstTestData.Close  'close record set
    Set rstTestData = Nothing  'destroys record set object
    Set dbTest = Nothing  'destroys database object
    Exit Sub

EH:        'If error occurs
    If Err.Number = 2501 Then
        Resume Exit_Here
    Else
        MsgBox Err.Number & vbCrLf & Err.Description
        Resume Exit_Here
    End If
End Sub

Note: "Value" is the default property in Access, so the ".Value" is not necessary (ex. If IsNull(Me.Tank.Value) = True Then)
In Visual Basic (and other languages), ".Value" is required. Not so in VBA.

[Micron]

http://access.mvps.org/access/forms/frm0003.htm
Interesting syntax and thanks for the edification, but I see it as a way of looping through controls whose names don't follow any convention that a seasoned db designer would ever allow. In fact, if I have a bunch like Text1, Text2 to Text51 because that's how Access names stuff, what if I want to iterate through 1 to 9, skip 11 to 18 but include 21 to 31? I'll never use it...

[NightWalker]

Micron,

What should I be using for my control naming conventions. I have been trying to clean that up as I go along to have all textboxes as txt***, comboboxes as cbo*** and such like that for the other controls. Is that what you are referring to or is there another way that a seasoned DB designer would do it? I am wanting to learn so I don't have to try to change things later.

Thank you for all your help.

[Micron]

Yes, that is what I meant, and it seems like you are on the right track. Here's what I refer people to on the subject
http://www.access-programmers.co.uk/...d.php?t=225837
I'm about the same as most except I use cmb for combo and mdl for module, and that's only because when I was learning, I knew the importance of adopting such a technique but didn't know all the so called 'standards'. I think if you're consistent and you never have to wonder what the name prefixes stand for, you're on your way. Even better if others can understand it in case you need help.

[NightWalker]

Yes that is where I am headed. I have a cheat sheet next to my computer for all the standard prefixes. There are many that say to put the _ (underscore) between naming conventions and the actual name. What is your take on this?

[Micron]

I almost never use the underscore. I see it as a keyboard character that is more trouble to find and hit as often as I needed it. I use what I think they call camel text, though I have no idea why they call it whatever it is. So cmdCustAdd or strOrderNum - where the first letter of every word is capitalized, but I preface with the control type short form name. Variables are usually prefixed as to type (var, lng, int, dte, str...) the exception being sql since I know that's always a string (what else could it be?). Field names are camel text as well, using short as possible letter combinations that still make sense to me at the time, and hopefully long after - WoStatus is work order status for example, but short form versions as much as possible. That's my take.

[NightWalker]

I am using all those same things now but I didn't know of before and I have been trying to cut out as many keystrokes as possible so I have started taking out all the underscores. I would say they call it camel text because it looks like it has humps like a camel LOL. I need to figure out more on how to shorten my field names because I have some really long ones and it sucks typing them out in vba over and over again. Once again thank you for all your help with everything I have put on here so far. I have learned a lot in a short amount of time.

[Micron]

Your welcome, and I think you're on to something about the camels. Either that, or the names look ugly too!
If you want to provide some example field names that are too long, post some for feedback. If not, that's OK.
Good luck!

[NightWalker]

Here are some examples from just one table:

ProductionItem
ProductionItemPartNumber
ProductionItemNSN
ProductionItemType
ProductionItemHoistUse
ProductionItemProcedureNumber
ProductionItemId

[ssanfu]

How about

LONG NAME	SHORT(er) NAME
ProductionItem	ProdItem
ProductionItemPartNumber	ProdItemPartNum
ProductionItemNSN	ProdItemNSN
ProductionItemType	ProdItemType
ProductionItemHoistUse	ProdItemHoistUse
ProductionItemProcedureNumber	ProdItemProcNum
ProductionItemId	ProdItemId

[NightWalker]

I started doing that on my newer stuff but in the beginning I put field names longer than these. Then I started getting into trouble because I would change the captions in the tables. That caused problems for when I was trying to use VBA because I would put the field name that was in the header and that was not correct LOL. Learned lots of things the hard way LOL. I appreciate all of the Gurus here that have put up with my posts.

[Micron]

@Nightwalker, those are too easy. But I'm even lazier than Steve: ProdItemPartNum > ProdItemPN or even ProdItm... If it works phonetically, you will probably be able to understand the name a long time later. Who needs the 'e' in item in this newfangled texting world anyway?

[ssanfu]

Hey! I'm not lazy...... I just conserve the maximum amount of energy I can.

[Micron]

Well, I say if you're going to weigh in on how not to type the underscore character the shoe must fit, no?