Solved Trouble adapting Uncle Gizmos Checklist code. (1 Viewer)

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 10:08
Joined
Apr 1, 2019
Messages
712
Hi, I've done the tutorial kindly & thoroughly covered by Uncle Gizmo on his website dealing with the set up of checklists. I'm a bit 'green'with VBA so I don't understand a lot!. I'm hoping that someone familiar with this code can help. I do not wish to post the full code as I think it is Uncle Gizmos intention for me to learn the code myself rather than simply copy & paste exercise. I respect that.
I have 2 problems;
1) I've copied this to a standard module;

Code:
Option Compare Database
Option Explicit
Private Function fcopyListToData(lngMasterID As Long)
On Error GoTo Error_Handler
    Dim strSubName As String
    Dim strModuleName As String
    Dim curDB As DAO.Database
    Dim rsList As DAO.Recordset
    strSubName = "FcopyListToData"
    strModuleName = "Form - " & Me.Name

    Set curDB = CurrentDb

    Dim lngSet As Long
    Dim lngID As Long

            Set rsList = curDB.OpenRecordset(fSQL_List, dbOpenForwardOnly)
        
                Do Until rsList.EOF
                    lngSet = rsList!listSets
                    lngID = rsList!ListID
                
                    Call fAppendListToData(lngMasterID, lngSet, lngID)
                
                    rsList.MoveNext
                Loop
Exit_ErrorHandler:

    Exit Function

Error_Handler:
'
Resume Exit_ErrorHandler

End Function
I get a compile error at me.Name!! Why?

2) I dont't understand strSQL3 of the following module;

Code:
Private Function fSQL_Data(lngMasterID As Long) As String
On Error GoTo Error_Handler

    Dim strSQL0 As String
    Dim strSQL1 As String
    Dim strSQL2 As String
    Dim strSQL3 As String
    Dim strSQL4 As String
       
  strSQL1 = "SELECT datalinkID,datasets,dataItems,DataTickedOff "
  strSQL2 = "FROM tblData "
  strSQL3 = "Where (((datalinkID)=1" ' this is used for 1 only....I think this is an error.

  strSQL4 = "));"
 
  strSQL0 = strSQL1 & strSQL2 & strSQL3 & strSQL4
 
  fSQL_Data = strSQL0
  'Debug.Print fSQL_Data
Exit_ErrorHandler:
   
    Exit Function
   
Error_Handler:
'
Resume Exit_ErrorHandler
 
End Function
Surely, this should be a variable? I put a 1 in it to test but I think should be a variable.
3) in the tutorial the "code" to fill the subforms was called from the "Current" event but in a later tutorial the code was called from the "on Load" event. What do I do?
 
Last edited by a moderator:

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:08
Joined
Aug 30, 2003
Messages
36,118
I'm not familiar with his sample, but Uncle is probably asleep. You can't use "Me" in a standard module. It is used as a shortcut to refer to the object containing the code, ie a form or report. It appears the code is supposed to be in a form's module.

Given the context, I expect the code for strSQL3 is supposed to use the ID that is passed to the function. Like:

strSQL3 = "Where (((datalinkID)=" & lngMasterID

As to the events, the load event will only run when the form first opens, the current event will fire at that time plus any time the displayed record changes. Use whichever is appropriate to your situation.
 

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 10:08
Joined
Apr 1, 2019
Messages
712
pbaldy, thanks for the prompt response. I'll give both of those recommendations a go. I thought it was something like that.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:08
Joined
Aug 30, 2003
Messages
36,118
No problem, post back if you get stuck. Hopefully I've made good assumptions.
 

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 10:08
Joined
Apr 1, 2019
Messages
712
pbaldy, You're suggestion seemed to work. Thanks. Got another problem though, See code that is on "On Load" event of main form. It is supposed to point the record source of each of my 5 subforms at a different data set.

Code:
Private Sub Form_Load()
'LocationID  Location
'1   Inspection 1
'2   Inspection 2
'3   Inspection 3
'4   Inspection 4
'5   Inspection 5

Me.subFrm1.Form.RecordSource = "SELECT tblData.DataID, tblData.DataLinkID, tblData.dataSets, tblData.dataItems, tblData.DataTickedoff FROM tblData WHERE (((tblData.dataSets)=1));"
Me.Label1.Caption = "Inspection 1"
Me.subFrm2.Form.RecordSource = "SELECT tblData.DataID, tblData.DataLinkID, tblData.dataSets, tblData.dataItems, tblData.DataTickedoff FROM tblData WHERE (((tblData.dataSets)=2));"
Me.Label2.Caption = "Inspection 2"
Me.subFrm3.Form.RecordSource = "SELECT tblData.DataID, tblData.DataLinkID, tblData.dataSets, tblData.dataItems, tblData.DataTickedoff FROM tblData WHERE (((tblData.dataSets)=3));"
Me.Label3.Caption = "Inspection 3"
Me.subFrm4.Form.RecordSource = "SELECT tblData.DataID, tblData.DataLinkID, tblData.dataSets, tblData.dataItems, tblData.DataTickedoff FROM tblData WHERE (((tblData.dataSets)=4));"
Me.Label7.Caption = "Inspection 4"
Me.subFrm5.Form.RecordSource = "SELECT tblData.DataID, tblData.DataLinkID, tblData.dataSets, tblData.dataItems, tblData.DataTickedoff FROM tblData WHERE (((tblData.dataSets)=5));"
Me.Label5.Caption = "Inspection 5"
End Sub

It does not populate subFrm2 onwards with datasets = 2 etc. I pretty much copied this from the Uncle Gizmo tutorials. If I substitute 1 for 2 it populates subform 2 with data from dataset =1 OK. Each subform says 1 of 5 (which is correct) but i think the records are not being displayed

Any Ideas?
 
Last edited by a moderator:

moke123

AWF VIP
Local time
Today, 17:08
Joined
Jan 11, 2013
Messages
3,852
not sure if this is a typo but it your trying to concatenate a form module name like Form_Form1 this is wrong

strModuleName = "Form - " & Me.Name

should be an underscore not a dash
strModuleName = "Form_ " & Me.Name
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 21:08
Joined
Jul 9, 2003
Messages
16,245
I get a compile error at me.Name!! Why?

You will get an error if you use that code in a module. There should be two lines of code:-

strModuleName = "Form - " & Me.Name 'Form
'AND
strModuleName = "basMyModuleName" 'Module

One for when you put the function in a module and one for when it's in the form. If you are using the version of the code that works in a form, in a module you will get an error...

You just comment out the one you are not using, it makes your code very portable between a Form and a Module depending. Especially useful when you're developing.
 

moke123

AWF VIP
Local time
Today, 17:08
Joined
Jan 11, 2013
Messages
3,852
As good as!

It's my birthday,and I've been celebrating!

I'm just off to bed!

Not according to AWF.

bday.JPG


Happy Birthday U.G.! 🍰
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 21:08
Joined
Jul 9, 2003
Messages
16,245
Not according to AWF.

I don't record my birthday anywhere on the internet, (not where people can easily find it) and use it for nefarious purposes, like setting up bank accounts in my name,which has been done in the past!
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 21:08
Joined
Jul 9, 2003
Messages
16,245
I pretty much copied this from the Uncle Gizmo tutorials.

I don't recognise that as a tutorial that I did? It looks to me more like one step in the process of developing the code. Developing the code from a long drawn-out massive text, as shown there, developing it so that it turns into a simple function. I think the term to describe this process is "refactoring" ... So I'd be interested to know so what tutorial you are looking at so I can address the issues you are having...
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:08
Joined
Aug 30, 2003
Messages
36,118
It does not populate subFrm2 onwards with datasets = 2 etc. I pretty much copied this from the Uncle Gizmo tutorials. If I substitute 1 for 2 it populates subform 2 with data from dataset =1 OK. Each subform says 1 of 5 (which is correct) but i think the records are not being displayed

I don't see where this has been addressed, but I just got off the road from a 7 hour drive. Offhand the code looks appropriate. Can you attach the db here to play with?
 

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 10:08
Joined
Apr 1, 2019
Messages
712
pbaldy, please see attached. I appreciate your perseverance. Also in the 'copy list to data' it seems to copy the most recent entry twice? Have a look at tblData.
 

Attachments

  • Checklist.zip
    49.2 KB · Views: 508

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 21:08
Joined
Jul 9, 2003
Messages
16,245
It looks to me more like one step in the process of developing the code.

Multiple Checklist - Generic Solution - Nifty Access

Now the really annoying thing is I remember building that Damned function, but it's not in the folder where it's supposed to be, which means I built it in one of my other folders and now I can't find it!
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 21:08
Joined
Jul 9, 2003
Messages
16,245
Here's the "Refactored" Code (Not sure that's the correct use of that term)
Code:
Dim curDB As DAO.Database
Dim rst As DAO.Recordset

Set curDB = CurrentDb

Dim strSQL_RSL As String
Dim strSetID As String
Dim strSetName As String

Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String
Dim strEND As String

Dim strSubFrmLbl As String
Dim strSubFrmWin As String

    strSQL_RSL = "SELECT SetID, SetName FROM tblSetNames"
  
    strSELECT = "SELECT dataID, dataLinkID, dataSets, dataItems, dataTickedOff "
    strFROM = "FROM tblData "
    strWHERE = "WHERE (((dataSets)="
    '& strSetID &'
    strEND = "))"
  
    strSubFrmLbl = "lblSubFrmWinSfrmData_"
    strSubFrmWin = "subFrmWinSfrmData_"
  
        Set rst = curDB.OpenRecordset(strSQL_RSL, dbOpenForwardOnly)
      
            Do Until rst.EOF
                strSetID = rst!SetID
                strSetName = rst!SetName
                  
                    If Len(strSetID) = 1 Then strSetID = "0" & strSetID
                  
                    Me(strSubFrmWin & strSetID).Form.RecordSource = strSELECT & strFROM & strWHERE & strSetID & strEND
                    Me(strSubFrmLbl & strSetID).Caption = strSetName
                  
                rst.MoveNext
            Loop
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:08
Joined
Aug 30, 2003
Messages
36,118
pbaldy, please see attached. I appreciate your perseverance. Also in the 'copy list to data' it seems to copy the most recent entry twice? Have a look at tblData.

I'm going to let Uncle address his code, as I haven't really tried to follow what the intent is. To your problem with the subforms, I think it has to do with the combo. I don't like datasheet view, can't figure out why all the fields aren't showing, don't have time to follow up (just got home from long drive, dinner in a few). If I change to continuous view, it looks like the appropriate records are in each subform. Something in your combo is not displaying the values in it:

1592271385972.png
 

Users who are viewing this thread

Top Bottom