LOL. Remains to be seen?
Of course I had euchre in mind.
(still laughing)
LOL. Remains to be seen?
Of course I had euchre in mind.
(still laughing)
See http://access.mvps.org/access/forms/frm0003.htm
@NightWalker
Haven't tested this, but it should work
Note: "Value" is the default property in Access, so the ".Value" is not necessary (ex. If IsNull(Me.Tank.Value) = True Then)Code: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
In Visual Basic (and other languages), ".Value" is required. Not so in VBA.
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...
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.
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.
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?
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.
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.
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!
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
Here are some examples from just one table:
ProductionItem
ProductionItemPartNumber
ProductionItemNSN
ProductionItemType
ProductionItemHoistUse
ProductionItemProcedureNumber
ProductionItemId
How about
LONG NAME
SHORT(er) NAME
ProductionItem ProdItem ProductionItemPartNumber ProdItemPartNum ProductionItemNSN ProdItemNSN ProductionItemType ProdItemType ProductionItemHoistUse ProdItemHoistUse ProductionItemProcedureNumber ProdItemProcNum ProductionItemId ProdItemId
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.
@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?
Hey! I'm not lazy...... I just conserve the maximum amount of energy I can.
Well, I say if you're going to weigh in on how not to type the underscore character the shoe must fit, no?
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.