Form/subform reference (was I'm a Luddite)

[alanquick]

Hi

I've built a DB in access from scratch and almost ready to start using it but have one last problem to solve. In the attached completely stripped back DB I have the following issue

If you open frmTaskDetail you can see that if I choose a discipline I can match the tasks to the discipline and it all works perfectly, but when i drop it into frmMOS_main, in the ‘Quote Detail’ Tab either as a form or subform I get an error asking for a parameter when I click on Task

No matter what i do I can't rectify this, but then again, this has been built on youtube videos :-(

Can anyone help?

Alan

 

[pbaldy]

The syntax changes when the form is a subform:

http://access.mvps.org/access/forms/frm0031.htm

FYI, I moved your post to a more appropriate forum.

 

[sneuberg]

In the row source for the combo box i.e.,

SELECT tblTask.Task, tblTask.TaskID, tblTask.DisciplineID
FROM tblTask
WHERE (((tblTask.DisciplineID)=[Forms]![frmTaskDetail]![cboDiscipline]))
ORDER BY tblTask.TaskID;

the reference

[Forms]![frmTaskDetail]![cboDiscipline]

would have to be change to reflect it's in a subform, the model in the link provided by PBaldy would be.

Forms!Mainform!Subform1.Form!ControlName

If you redo the reference in the design view (grid view) Intellisense will help you get this right.

 

[alanquick]

Tried that still doesn't want to work��

 

[static]

You say it works perfect, but I only get tasks with discipline id 2 because that's the id of the first record that loads.
You need to use the on current event to refresh or reload the recordsource.
Because oncurrent refers to the form itself you can remove the worry of how forms relate by using ME, and then you can drop the form anywhere.

Dim DiscID As Long

Private Sub Form_Current()
    'check if DisciplineID has changed and if so refresh the list
    If DiscID <> cboDiscipline Then
        DiscID = cboDiscipline
        
        Dim sql As String
        sql = sql & "SELECT Task, TaskID, DisciplineID"
        sql = sql & " FROM tblTask WHERE DisciplineID=" & me!cboDiscipline
        sql = sql & " ORDER BY TaskID;"
        cboTask.RowSource = sql
    End If
End Sub

 

[alanquick]

OK, Remember the bit where I said I was a luddite. Tried using this but it comes back with.....
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', or 'UPDATE'.

AS you can see I really am dim at this stuff, but so close now to getting this going which is why its frustrating

 

[static]

Open your form frmTaskDetail in design view.
On the toolbar click 'view code'
Under option explicit paste the above code.
close the form and save changes.

Open the form in normal view.
The task dropdown should work.
Drag to another form as a subform. Still works?

Job done?

If not, please post your db again.

 

[jdraw]

This message

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', or 'UPDATE'.

suggests to me you have something set up as an ACTION query, but in fact you have a Select query.

 

[alanquick]

OK getting really close now so progress indeed, I don't get the error message any more, but...................... now the requery macro doesn't work so I can only see the same list of tasks for any of the disciplines. Updated DB attached

You must think I'm so thick:eek. :banghead::banghead:

 

[pbaldy]

You've hard-coded the discipline ID to 3 now.

 

[alanquick]

ok not sure what happened there and again getting closer, but the disciplines still don't match the tasks, see attached

 

[pbaldy]

Now we're back to where we started. The syntax to refer to the discipline combo doesn't account for the form being a subform.

 

[alanquick]

so how do i fix it? can you fix it and send it back and I can look at what you've done and copy it into my master file?

 

[static]

The tasks in the drop-down change to relate to the Discipline for me.
But the list isn't getting updated when the Discipline isn't selected or is changed. Fixed...

Dim DiscID As Long

Private Sub cboDiscipline_Click()
    Form_Current
End Sub

Private Sub Form_Current()
    If "" & cboDiscipline = "" Then
        'New record / Discipline not selected
        cboTask.RowSource = ""
    Else
    
        If DiscID <> cboDiscipline Then
            'check if DisciplineID has changed and if so refresh the list
            DiscID = cboDiscipline
            
            Dim sql As String
            sql = sql & "SELECT Task, TaskID, DisciplineID"
            sql = sql & " FROM tblTask WHERE DisciplineID=" & Me!cboDiscipline
            sql = sql & " ORDER BY TaskID;"
            cboTask.RowSource = sql
        End If
    End If
End Sub

 

[alanquick]

sorted!!!!! thanks so much