Solved CheckList Database Help Required (1 Viewer)

farhanleos

Registered User.
Local time
Today, 03:55
Joined
Oct 19, 2017
Messages
38
Gentlemen,

Greetings

i am working on one database where i have to use checklist to count the Progress, before implementing the true data i always practice on any new thing with the raw data once its completed i understand completely and then i implement with the Right fields and data..

as practice i was trying to implement checklist on the attached database but at the end i mixup things in module and form event

Help required from professionals if they can see it and help me out ... it will be very helpful if this one come in working condition

THanks
 

Attachments

  • CheckListDB-003.accdb
    492 KB · Views: 282
Last edited by a moderator:

Guus2005

AWF VIP
Local time
Today, 11:55
Joined
Jun 26, 2007
Messages
2,645
Please compile your code before posting.

To compile: Open VBA window (alt-f11), menu: Debug, Compile Database3

You use:
End Sub instead of Exit Sub,
Case1 instead of Case 1
You need to add a reference to ADO or DAO
...

HTH:D
 

farhanleos

Registered User.
Local time
Today, 03:55
Joined
Oct 19, 2017
Messages
38
I add DAO m and change eveb case1 in place of cae 1

this is not the issue with it,
I really have to resolve this database , the concept of is working is
when we select the master id from form ,
it will auto fill the tblData with againt the mastr id 1 , it shows all data from tblList with list set 1

I need to help to turn it to working :(
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:55
Joined
May 7, 2009
Messages
19,175
here is the fixed db.
 

Attachments

  • CheckListDB-003.accdb
    532 KB · Views: 256

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 10:55
Joined
Jul 9, 2003
Messages
16,245
Nice Arnel!

Code:
Public Function SQLFIX(value As Variant) As String
'//// ATH Added - this is Ariel's code
    Select Case VarType(value)
    Case VbVarType.vbBoolean, VbVarType.vbByte, VbVarType.vbCurrency, _
        VbVarType.vbDouble, VbVarType.vbDecimal, VbVarType.vbInteger, _
        VbVarType.vbLong, VbVarType.vbSingle
        SQLFIX = value
    Case VbVarType.vbDate
        SQLFIX = "#" & Format(value, "mm/dd/yyyy") & "#"
    Case VbVarType.vbString
        SQLFIX = Chr(34) & value & Chr(34)
    Case VbVarType.vbNull
        SQLFIX = "Null"
    End Select
End Function
 
Last edited:

farhanleos

Registered User.
Local time
Today, 03:55
Joined
Oct 19, 2017
Messages
38

Attachments

  • Capture.JPG
    Capture.JPG
    20.7 KB · Views: 348

Solo712

Registered User.
Local time
Today, 06:55
Joined
Oct 19, 2012
Messages
828
sir on clicking form I got this error ..

You get this error because you called a function which invoked Screen.ActiveForm before the form was displayed (i.e. before the OnCurrent Event was finished). The line causing the error is :
Code:
 strModuleName = "Form - " & Screen.ActiveForm.Name
in the fCopyListToDataOnlyOnce. It gets caught in the OnCurrent error handler because the function's own error handler was set up after this line executed.

Incidentally, Screen.ActiveForm returns a form, not an active module. If you want to trace your errors quickly, you should write the name of the module as a literal i.e. as
Code:
strModuleName = "basCopyListToData"

Best,
Jiri
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:55
Joined
May 7, 2009
Messages
19,175
Here again is the db
 

Attachments

  • CheckListDB-003.accdb
    672 KB · Views: 279

farhanleos

Registered User.
Local time
Today, 03:55
Joined
Oct 19, 2017
Messages
38
You get this error because you called a function which invoked Screen.ActiveForm before the form was displayed (i.e. before the OnCurrent Event was finished). The line causing the error is :
Code:
 strModuleName = "Form - " & Screen.ActiveForm.Name
in the fCopyListToDataOnlyOnce. It gets caught in the OnCurrent error handler because the function's own error handler was set up after this line executed.

Incidentally, Screen.ActiveForm returns a form, not an active module. If you want to trace your errors quickly, you should write the name of the module as a literal i.e. as
Code:
strModuleName = "basCopyListToData"
Best,
Jiri



Solo thanks for your reply, but by that change Its not working I got more errors.
have you tried ?
 

CuriousGeo

Registered User.
Local time
Today, 06:55
Joined
Oct 15, 2012
Messages
59
Hello, renewing this post. I am trying to recreate this idea in a database. I have downloaded this sample database (CheckListDB-003). I have also viewed Uncle Gizmo's tutorials. The problem I am running into is in this sample database, when you add a new record, the list of items does not get added automatically. It doesn't appear until you either navigate away from that record or close the form and reopen it.

Shouldn't this sample database have a requery? Am I missing something here?

On Uncle Gizmo's tutorial "Checklist Extras, Update the Checklist Automatically" you demonstrate how to add a requery of the subform. It works in your video, however, when I try to recreate your code, it does not work. May I have some assistance?
 

Mark_

Longboard on the internet
Local time
Today, 03:55
Joined
Sep 12, 2017
Messages
2,111
What error do you get?
Where are you trying to requery? Please post the code that you have an issue with.
 

CuriousGeo

Registered User.
Local time
Today, 06:55
Joined
Oct 15, 2012
Messages
59
Hi Mark, there is an After Update event for the masterDescription field that is supposed to Requery the subform so that a list of items get added to the record in that subform. I have attached some screen captures and here is the code:

Code:
Private Sub txtmasterDescription_AfterUpdate()

    If IsNull(Me.txtmasterDescription) Or Me.txtmasterDescription = "" Then
        MsgBox "masterID is required"
        Exit Sub
    End If
    
    Call fCopyListToDataOnlyOnce(Me.masterID)
    sfrmWinSfrmData.Requery
    
End Sub

"form" shows a record with the list, "formnewrecord" shows a blank new record.

The Requery does not work as it should, until you leave that new record and come back to it, or you close the form and reopen it.

The video link I refer to in Post #12 shows Uncle Gizmo (Tony) using the Requery in his demonstration, and it works as it should.
 

Attachments

  • form.jpg
    form.jpg
    40.4 KB · Views: 207
  • formnewrecord.jpg
    formnewrecord.jpg
    95.3 KB · Views: 254

Mark_

Longboard on the internet
Local time
Today, 03:55
Joined
Sep 12, 2017
Messages
2,111
If you are not seeing the requery, are you sure you code is being hit?

Before the requery, I would put in a msgbox just to make sure your code is actually being called. From there we can start narrowing down what is/isn't working or being called.
 

CuriousGeo

Registered User.
Local time
Today, 06:55
Joined
Oct 15, 2012
Messages
59
If you are not seeing the requery, are you sure you code is being hit?

Before the requery, I would put in a msgbox just to make sure your code is actually being called. From there we can start narrowing down what is/isn't working or being called.

Ok, I added a msgbox to the After Update code:

Code:
Private Sub fldDatePerformed_AfterUpdate()
    
    Call fCopyListToDataOnlyOnce(Me.masterID)
    
    Me!subFrmWinData.Requery
    
    MsgBox "Date Entered", vbOKOnly
    
End Sub

I also changed the Requery line, because I was reading on "The Access Web" about how to reference subforms

The message box opens as it should, but the Requery still does not happen unless you either close the form or navigate to the previous record then go back to the newest record.

I will attach the database and see if anyone can figure it out. Open frmMaster
 

Attachments

  • Stains List QC.accdb
    776 KB · Views: 206
Last edited:

Mark_

Longboard on the internet
Local time
Today, 03:55
Joined
Sep 12, 2017
Messages
2,111
OK, stepping through I've found its not a problem with the refresh, it is a problem with fCopyListToData. It gets called once before the refresh, but it also gets called again when you come back to the form. There are no new records after the first pass.
 

CuriousGeo

Registered User.
Local time
Today, 06:55
Joined
Oct 15, 2012
Messages
59
Thanks for taking a look Mark. I wonder why if the "fCopyListToData" gets called the first time nothing happens, but the second time it adds the list. Do you have any idea as to how to fix the "fCopyListToData" problem?

I have sent Uncle Gizmo a message to see if he can offer any help. He was the one that came up with this idea and has tutorials showing how it works.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 10:55
Joined
Jul 9, 2003
Messages
16,245
fCopyListToData copies the list over. It will only do this once, but only if you have a unique index in the receiving table... However, I wasn't happy with this function being called every time a record was amended because if the index was removed, then existing data could possibly be corrupted. To safegard this, I added fCopyListToDataOnlyOnce which checks to see if the list already exists. It it does exist, then the "copy" is cancelled ...
 

CuriousGeo

Registered User.
Local time
Today, 06:55
Joined
Oct 15, 2012
Messages
59
Thanks for replying Uncle Gizmo. Is it possible for you to take a look at my database, with the changes I made and see if you can find anything wrong? It's in post 16 above. I deleted the checkbox from the original example and replaced it with a combo box of possible responses. Could that be part of the problem?
 

Users who are viewing this thread

Top Bottom