Help with After Update code

[Missus_Nat]

Hello All!

I want to put an 'After Update' code (or possibly an 'On Change' code) on my Text Boxes so that changing the auto-filled data in the Form updates the record, rather than creating a new record.

Here is the After Update code I am using in my Combo Box to produce the auto-fill data:

Private Sub Course_AfterUpdate()
Me.[Date of Training] = Me.Course.Column(2)
Me.Location = Me.Course.Column(3)
Me.Instructor = Me.Course.Column(1)
Me.Grade = Me.Course.Column(4)
Me.Certificate = Me.Course.Column(6)
End Sub

This part works great, but when I change the displayed data (ex. change the date from 2010 to 2012), it creates a new record in the Table. In this example I want it to replace 2010 with 2012 in the existing Record.

Any help would be aweso-rific!!

 

[boblarson]

I think something else you have somewhere is causing the new record. Just changing the text box data ([Date of Training]) isn't going to create a new record. Post the entire module from that form so we can see what all is happening on the form.

OH, and USE CODE TAGS when posting code to the forum:

 

[Missus_Nat]

Thanks for the tip about code tags!! I am totally new to this.

I'm not sure what you mean by 'post the entire module', so I'm posting a zipped copy of my database... The form I am working on is called 'Training Records'.

View attachment Training - Copy.zip

I've tried playing with the overall Form 'Data' settings, such as Allow Additions and Data Entry, but my knowledge of Access isn't enough for me to know how to get the result I want.

Also, I'm aware that my db is probably not set up how a progammer would do it, but everything except this one hiccup works exactly how I want.

Many thousands of apologies for my lack of experience with this!!!!!!!!

 

[boblarson]

I won't be able to see this for about another 10 hours until I get home. So, if anyone else can take a look feel free.

As for posting the code, all you need to do is go to the VBA window to the code you posted in the first post. Then hold your CTRL key down and press A at the same time and it will select everything in that module. Then come here and then you can paste (CTRL+V) and then add the code tags like I showed.

 

[Missus_Nat]

Ok, I opened my Form in Design View and clicked the 'View Code' button; this is what I have:

Option Compare Database

Private Sub Course_AfterUpdate()
Me.[Date of Training] = Me.Course.Column(2)
Me.Location = Me.Course.Column(3)
Me.Instructor = Me.Course.Column(1)
Me.Grade = Me.Course.Column(4)
Me.Certificate = Me.Course.Column(6)
End Sub

Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
End Sub

Private Sub Student_ID_AfterUpdate()
Me.Course = 0
Me.Course.Requery
End Sub

Did I use the code tags correctly??
Sorry I'm such a newbie!!

 

[boblarson]

1. you did use the code tags correctly.

2. Your Form_Load event is moving to a new record so you would be on a new record and that could be why you are ending up with a new record if you are selecting the Course combo after opening up the form and not navigating to the record you want to update.

Also, not sure why you have this:

Private Sub Student_ID_AfterUpdate()
Me.Course = 0
Me.Course.Requery
End Sub

What is that supposed to do? Why would you be setting the value of Course for a record to 0 and then requerying the combo box?

 

[Missus_Nat]

1. Awesome, thanks!!

2. How do I change the Form_Load event so that it opens blank, but not on a New Record?

3. Someone else gave me this code... In effect, once I select a Student ID, it limits my Course Combo Box selections to only the ones associated with that particular Student ID.

 

[boblarson]

2. How do I change the Form_Load event so that it opens blank, but not on a New Record?

Umm, that isn't possible. If you don't want an existing record to be there you need to set it to a new record.[/quote]

3. Someone else gave me this code... In effect, once I select a Student ID, it limits my Course Combo Box selections to only the ones associated with that particular Student ID.

Setting it to 0 isn't something you should do. You can requery it but setting to 0 isn't good if it is bound to something.

Are you trying to use one of the controls to go to a record? It sounds like maybe you are wanting to select someone from the combo box and then go to their record. The code you have doesn't do that. It just will create a new record.

 

[Missus_Nat]

What I'm trying to do is select a Student ID, then have the Course selections limited to the ones for that Student ID, then auto-fill the other fields. (this is working right now)

Then I want to change some of the data (ex. put in a new date), and have it update that record, rather than creating a new one.

Basically, I don't want to have to navigate to a record, I want to just select it for editing.

It's been quite a battle to get this to work, but it would save me SO much time.

Thank-you again, and sorry if that doesn't make sense!!

 

[boblarson]

Can you upload a screenshot, or two, to the forum (not a public pic site as those are blocked for me) showing your form so I can see what you are talking about?

 

[Missus_Nat]

I tried to physically paste it in, but it said my text was too long.. Hopefully you can open the jpeg.

THANK-YOU!

 

[Missus_Nat]

Hello again,

I've been scouring the forum trying to find other threads on what I want to do.
This one sounds like what I want, but I am not sure how to add more code to the After Update Event of my last combo box, as I already have code there to auto-fill the other Fields in the Form.
This might also be related, but I REALLY don't want to rebuild my entire form, as it has already taken me hours and hours and hours. And hours.


I've gone back to the default On Load settings (instead of opening to a new record), and set the Allow Additions setting to 'No', so my Form can't make any new records.

How do I make selections in my cascading Combo Boxes without changing the current record? (I want to navigate to a specific Record by making selections in the Combo Boxes, rather than having to search for the Record)

Sorry again for my complete ineptitude!!!