Solved CheckList Database Help Required

F

farhanleos

Registered User.
Local time
Yesterday, 18:54
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

Last edited by a moderator:
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
 
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 :(
 
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:

Attachments

  • Capture.JPG
    Capture.JPG
    20.7 KB · Views: 391
farhanleos said:
sir on clicking form I got this error ..
Click to expand...

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:
Solo712 said:
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
Click to expand...



Solo thanks for your reply, but by that change Its not working I got more errors.
have you tried ?
 
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?
 
What error do you get?
Where are you trying to requery? Please post the code that you have an issue with.
 
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: 250
  • formnewrecord.jpg
    formnewrecord.jpg
    95.3 KB · Views: 294
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.
 
Mark_ said:
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.
Click to expand...

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

Last edited:
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.
 
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.
 
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 ...
 
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?
 
You must log in or register to reply here.

Similar threads

A
Solved Checkbox not working on checklist
Replies
9
Views
555
AlliCarr
A
A
Solved Referring to a control on a subform from the parent form.
Replies
3
Views
231
AlliCarr
A
G
Performance Issues with FAYT Combobox Using Linked SQL Server Table (20.000+ Records)
Replies
8
Views
347
CJ_London
C
E
Best Practices for User Access Control in Access Databases - Emmanuel Katto Uganda
Replies
3
Views
237
Pat Hartman
P
nimeshaanethmini
Need Help with VBA Code for Access Form Filtering
Replies
5
Views
389
Uncle Gizmo
Uncle Gizmo

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom