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

[HillTJ]

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;

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;

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?

 

[pbaldy]

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]

pbaldy, thanks for the prompt response. I'll give both of those recommendations a go. I thought it was something like that.

 

[pbaldy]

No problem, post back if you get stuck. Hopefully I've made good assumptions.

 

[HillTJ]

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.

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?

 

[moke123]

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]

Uncle is probably asleep.

As good as!

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

I'm just off to bed!

 

[Uncle Gizmo]

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]

As good as!

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

I'm just off to bed!

Not according to AWF.

Happy Birthday U.G.!

 

[Uncle Gizmo]

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!

 

[NauticalGent]

Happy birthday Tony!

 

[Uncle Gizmo]

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]

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?

 

[pbaldy]

As good as!

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

I'm just off to bed!

Must have been some celebration, it looks like you lost a day!

 

[Uncle Gizmo]

Must have been some celebration,

Somebody bought me a bottle of Southern Comfort, I now have a half-empty bottle of Southern Comfort!

 

[HillTJ]

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.

 

[HillTJ]

Uncle Gizmo, happens to me too!

 

[Uncle Gizmo]

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]

Here's the "Refactored" Code (Not sure that's the correct use of that term)

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

 

[pbaldy]

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: