Insert (1 Viewer)

mike60smart · Sep 9, 2017

Hi Everyone

In the attached example Db I have a Main Form named "frmClients"

With a Subform named "frmEventDetailsSub"

Nested within the Subform is a Form named "frmEventTasksSubform"

On the Subform there is a Combobox named "cboType" which select an EventType.

Also on the subform is an Unbound Listbox named "ListTypes" which displays all of the Tasks associated with the Event Types.

What I am trying to do is on the AfterUpdate of cboType insert into tblEventTasks all of the EventTypeTaskListID's which are displayed in the Unbound Listbox.

The AfterUpdate code on the Combobox does nothing at the moment.

Any help appreciated.

View attachment Events.zip

moke123 · Sep 9, 2017

hi mike,
not sure i'm following you but have a few observations.

Code:

INSERT INTO tblEventTasks (EventTypeTaskListID)

there is no EventTypeTaskListID field in tblEventTasks

your iterating through Me.ListTypes.ItemsSelected but it is not a multiselect listbox

mike60smart · Sep 10, 2017

Hi moke

Sorry my mistake it should be :-

INSERT INTO tblEventTasks (EventID, TaskID)

The Listbox is just displaying a list of all associated Tasks for the Event selected in the Combobox. I did not make it a MultiSelect as the user would not be actually making any selection.

I am trying to get the correct list inserted into tblEventTasks as the list will always be the same for that particular Event.

The code I am using may not be of any use and looking for any better option.

Hope that clears the mud slightly :banghead:

mike60smart · Sep 10, 2017

Hi moke

I have modified the code and the table name in the attached.

I have made an attempt to add a WHERE to the SQL but my syntax is wrong.

View attachment Events (2).zip

arnelgp · Sep 10, 2017

here take a look.

mike60smart · Sep 10, 2017

Hi arnelgp

When I open the Db I get an error ref the macro.

The macro is supposed to Open qryTasksToInsert but this query is not available??

arnelgp · Sep 10, 2017

i am sorry, this i download
from your first post.

what does your query do?
there is no such query on that db.

you can check your form, using
AfterUpdate of your combo on the
first subform.

mike60smart · Sep 11, 2017

Hi arnelgp

My apologies it was a macro that was trying to run on Load of the Form.

I removed it and everything now working just great

Many thanks for your help

mike60smart · Sep 19, 2017

Hi arnelgp

It looks like the Insert is not executing as expected.

For each Event I select it should insert a different list of tasks

What it currently does is insert the same list every time no matter which Event has been selected??

moke123 · Sep 19, 2017

For each Event I select it should insert a different list of tasks

How do we determine which tasks get associated with each event

edit:
Ok, so there are 11 tasks for eventtype 1, and 5 for 2?

moke123 · Sep 19, 2017

maybe something like this?

Code:

Private Sub cboType_AfterUpdate()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSql As String
    Dim InsSql As String
    Dim ETID As Long

    ETID = Me.cboType.Column(0)

    strSql = "select * from tblTasks"
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSql)

    Do Until rs.EOF
        If rs!EventTypeId = ETID Then
            InsSql = "Insert into tblEventTasks(EventID,TaskID) values( " & Me.EventID & "," & rs!TaskID & ")"
            db.Execute InsSql, dbFailOnError
        End If

        rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

mike60smart · Sep 19, 2017

Hi moke123

Tried the code you suggested and get the following error:-

When I hit the debug button it highlights this line:-

I have attached the modified Db:-

View attachment Events Insert Tasks.zip

moke123 · Sep 19, 2017

your new form lacks an EventID field therefor Me.EventID is missing.

mike60smart · Sep 19, 2017

Hi moke123

Not following.

The Form is shown in Design View and EventID is showing in Green on the Event Details Subform ??

moke123 · Sep 19, 2017

ok, you had a different form as a start form so I thought you were working with that one.

did you change TaskID to EventTypeTaskListID in the table? I think thats the error.

mike60smart · Sep 19, 2017

Hi Moke123

Perfect That is just great works a treat.

Many Many thanks

Insert (1 Viewer)

mike60smart

Registered User.

moke123

AWF VIP

mike60smart

Registered User.

mike60smart

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Attachments

mike60smart

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

mike60smart

Registered User.

mike60smart

Registered User.

moke123

AWF VIP

moke123

AWF VIP

mike60smart

Registered User.

moke123

AWF VIP

mike60smart

Registered User.

moke123

AWF VIP

mike60smart

Registered User.

Similar threads

Users who are viewing this thread