×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Listbox update

Listbox update

Listbox update

(OP)
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?

RE: Listbox update

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

RE: Listbox update

(OP)
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

RE: Listbox update

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

RE: Listbox update

(OP)
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.

RE: Listbox update

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

RE: Listbox update

(OP)
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];

RE: Listbox update

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.

CODE --> SQL_Row_Source

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

RE: Listbox update

(OP)
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?

RE: Listbox update

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

RE: Listbox update

(OP)
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

RE: Listbox update

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?

CODE --> vba

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 ponder

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

RE: Listbox update

(OP)
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.

RE: Listbox update

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

RE: Listbox update

(OP)
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?

RE: Listbox update

(OP)
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

RE: Listbox update

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

RE: Listbox update

(OP)
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.

RE: Listbox update

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close