Listbox update 1

[olushow]

I have a listbox that would list activities related to a training event. The event is the master record, while the activities will be the child record based on the tables relationship. i have an "Add Activity" button that will create the related activity and populate the listbox instantly. How do I ensure that the listbox updates correctly, so for when I open the Training Events form to create a new event record, the listbox should be empty until i create the activity?

 

[dhookom]

Hi olushow,
What is the purpose of the list box? Does it simply display the activities that have been added to the training event? What is the code behind the "Add Activity" button?

Is there a lookup table of activities that can be added to a training event or is this freeform?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[olushow]

No there is no lookup table. This is freeform. I slso want to make sure that an activity can only be created only after the Event record has been created.. I want to avoid orhpan records, if you catch my drift....Here is the code that is behind the add activity button.

Private Sub btnAddActivity_Click()
DoCmd.OpenForm "frmTrainingActivity", acNormal, , , acFormAdd, acDialog
lstActivities.Requery
End Sub

 

[dhookom]

The lstActivities should be updated automatically because you have the Requery. This assumes your activity form is opened acDialog and the list box row source is based on the activities table.

Are you seeing something different?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[olushow]

Yes, when I open the main form Training Event it still shows records in the Listbox, even though the form is opened to add a new record. The listbox should be empty.

 

[dhookom]

Can you provide the Row Source of the list box? Does it filter by Event from the main form?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[olushow]

No it doesn't. i have a table called tblActivities which stores the data. The Primary key Events Id from the Training Events table is a foreign key on the tblActivities table. Now that i think about the filter makes no reference to the Training Events table.

SELECT tblTrainingActivity.[Activity ID], tblTrainingActivity.[Activity Type], tblTrainingActivity.[Activity Start Date/Time], tblTrainingActivity.[Activity End Date/Time], tblTrainingActivity.Integrator
FROM tblTrainingActivity
ORDER BY tblTrainingActivity.[Activity ID];

 

[dhookom]

I would expect the Row Source to be something like the following with updates to the WHERE clause based on your control and field names.

SELECT [Activity ID], [Activity Type], [Activity Start Date/Time], [Activity End Date/Time], Integrator
FROM tblTrainingActivity
WHERE EventID = [Forms]![YourEventFormName].[EventID]
ORDER BY tblTrainingActivity.[Activity ID];

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[olushow]

So I added the training table to the SQL Design. and the query now looks like this.

SELECT tblTrainingActivity.[Activity ID], tblTrainingActivity.[Activity Type], tblTrainingActivity.[Activity Start Date/Time], tblTrainingActivity.[Activity End Date/Time], tblTrainingActivity.Integrator
FROM tblTraining RIGHT JOIN tblTrainingActivity ON tblTraining.Event_ID = tblTrainingActivity.[Event ID]
ORDER BY tblTrainingActivity.[Activity ID];

Will this be sufficient? Or should i change to what you suggested?

 

[dhookom]

There is no reason to add tblTraining. Also, the list box is still not filtered based on your SQL. It will display every record in the activity table which is not what you want.

Either do as I suggested or create a small subform based on the activity table and use the Link Master Child properties with the Event ID fields.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[olushow]

That seemed to fix it.. At least to ensure that the listbox is empty when creating a new event. But when I create the activity it does not create the entry in the listbox. Here is my code behind the button'

Private Sub Command16_Click()
Saved = True
DoCmd.RunCommand (acCmdSaveRecord)
Me.btnSave.Enabled = False
Saved = False
End Sub

 

[dhookom]

olushow,
Please figure out how to use TGML and then Preview your posts. Formatted posts are much easier to read.

Is this still your code when you want to add an activity?

Private Sub btnAddActivity_Click()
    DoCmd.OpenForm "frmTrainingActivity", acNormal, , , acFormAdd, acDialog
    lstActivities.Requery
End Sub

When you open frmTrainingActivity how does it know which Event ID it is storing in tblTrainingActivity?

Which form contains Command16 and why didn't you give it a nice name

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[olushow]

My apologies that was the wrong command. Actually I have no code behind the Save button. I thought I did. - Can you make a suggestion please?

Thanks.

 

[dhookom]

I can't think of the last time I used a "Save" button. When you close a form or move to another record, the record is saved.

Again, how do you make sure the correct Event ID is being stored in tblTrainingActivity?

If you don't want to use any code, I would suggest you simply add a subform on your Event form to record the activities for that event.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[olushow]

I didn't think about that. I assumed it captured the value automatically, My question then is how do I capture the Event.ID to the Activity table/form?

 

[olushow]

So I have a button called Add Activity which is on the main form, that opens up the Training Activity form. My expectation is that once I create the record in The training activity form, it should automatically populate the activity table. I think I may have made this more complicated than it should be. I haven't done any VBA access work since 2006

 

[dhookom]

Do you have a reason for not using a subform? This is by far the easiest and most conventional method for adding child records that maintain a link/relationship to a main record/form.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[olushow]

I wanted to give the users the opportunity to edit and modify an activity, if needed be. I just assumed that could only be possible using a Listbox.

 

[dhookom]

List boxes aren't editable. That's why we have subforms.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016