Why isn't my combo box updating the form fields?

[John Big Booty]

OK it's a little more complex. What I've done is created a query to select all the stars that have been associated with a movieID and then made a second query (using the query wizard) to find all stars that don't match those found by the first query. I"ve also added a button to open a new form to allow a star to be selected and then added.

 

[John Big Booty]

Also I noticed that you used a minus sign (-) in a form name try to avoid this as it will cause you problems, use an underscore (_) instead.

Additionally don't just take my or anyone else's, for that matter, modifications as a done deal. Pull them apart and see what has been done, and try and work out what they have done and why. This way you may pick up a few tricks.

 

[oZone]

OK it's a little more complex. What I've done is created a query to select all the stars that have been associated with a movieID and then made a second query (using the query wizard) to find all stars that don't match those found by the first query.

WOW! Yeah, I wouldn't have been able to do that myself. At least not at this point in my noobness Thanks for the example, I'll look through it with a fine toothed comb and try to understand what you've done to get it to work.

Also I noticed that you used a minus sign (-) in a form name try to avoid this as it will cause you problems, use an underscore (_) instead.

OOPS! I think I meant to use an underscore, my bad. I'll keep a closer eye on that, thanks for pointing it out

Additionally don't just take my or anyone else's, for that matter, modifications as a done deal. Pull them apart and see what has been done, and try and work out what they have done and why. This way you may pick up a few tricks.

Thanks for your sound advice I do try to pull them appart and see what's been done, but sometimes I don't quite understand what was done. I'm here to learn, not to just be given the answers, which is why I'm struggling through this as much as I am. I'll take a look at what you've done to see if I can understand and apply it to future projects.

Thank you again for your valuable time and advice

 

[oZone]

What I've done is created a query to select all the stars that have been associated with a movieID and then made a second query (using the query wizard) to find all stars that don't match those found by the first query. I"ve also added a button to open a new form to allow a star to be selected and then added.

Did you add the queries to a form in the DB you attached? I looked through the DB but Form1 doesnt work. It doesn't show any actors in the combo boxes so I cant assign actors to any movie.

EDIT:
I figured out that you applied those queries to the "frmMovieDetails_comboBoxError_notworking" form. The combo box for that form doesnt populate the movie details when you select a movie from it. I'll take what you've done with the queries and try to apply them to Form1 and see where it gets me.

 

[oZone]

Okay, I transplanted the working stars list and your new Add Stars functions to Form1 and it's working great! Thanks for your help!

To take this form a step further, the final modification would allow the user to delete the movie if, for instance, they sold it or dont have it anymore.

I added a Delete Record command button to the form, but when I click it, it says "The record cannot be deleted because table 'tblStarsToMovies' includes related records."

How would I make this button delete the movie AND the related info in tblStarsToMovies?

I've attached the modified DB below.

 

[John Big Booty]

Personally I would not delete the record. I would add a check field to the Movies table, that is checked if the movie is sold, then just filter out any records that are checked. This would mean that if for some reason the user re-acquired the movie, all the data would still be on file. You could also use it to help prevent the user buying the movie again, if for example they got rid of it because they really didn't like.

 

[oZone]

Good idea. I just did that and it works great, thanks

I have one more issue with the movies form (Form1) that I need help with.

When you assign an actor to a new movie, the form navigates away from the movie you were just editing and displays the first record in the movies table. It doesnt stay focused on the new movie so the user can assign multiple actors.

So, if the user had to add 4 stars to a new movie, they can only add one star, then they have to select the movie they just added from the combo box and continue adding more stars.

How would I keep the form focused on the new movie (or stop it from navigating away from it) while the user adds as many stars as they want?

Help?

 

[John Big Booty]

Have a look at PBaldy's solution here.

 

[oZone]

Well, I gave it a good try, transplanted PBaldy's code into mine, but I haven't got it to work yet...

First, I'm not sure which form to put the code into, and second I'm not sure which value to reference. PBaldy references EmpID, which is a constant in his form. But my main form (Form1) uses a Query as its record source, and the form has a subform which opens up another form to add stars. The form to add stars to the movie also uses a Query as its record source. So, with all these queries, I'm not sure which value to use...

Also, with all these variables, I'm not sure which form/subform to place PBaldy's code in.

I tried adding his code to FRM_StarAdd (adds stars to the movie) and applied his code to the Add Star button. But that button already had some other code assigned to it so I did my best to integrate PBaldy's code with the existing code for this button.

Now, when I click the Add Star button Access gives me this error message:
"The expression you entered refers to an object that is closed or doesn't exist."

Here is the existing Add Star button code merged with PBaldy's code:

Private Sub Command2_Click()
'Begin PBaldy Code Insert--------------------------------------
    Dim rs            As Object
    Dim lngBookmark   As Long
'End PBaldy Code Insert ---------------------------------------
On Error GoTo Err_Command2_Click

    Dim stDocName As String

    stDocName = "QRY_AddStar"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    
    DoCmd.Close
    
    Forms!Form1.Requery

'Begin PBaldy Code Insert -------------------------------------
    'set a variable to the current record
    lngBookmark = Me.Name
    'requery the form
    Me.Requery

    'bring us back to the original record
    Set rs = Me.RecordsetClone
    rs.FindFirst "Name = " & lngBookmark
    Me.Bookmark = rs.Bookmark
'End PBaldy Code Insert ---------------------------------------

Exit_Command2_Click:
'Begin PBaldy Code Insert -------------------------------------
    Set rs = Nothing
'End PBaldy Code Insert ---------------------------------------
    Exit Sub

Err_Command2_Click:
    MsgBox Err.Description
    Resume Exit_Command2_Click
    
End Sub

I'll attach my updated DB below. Could someone please tell me what I'm doing wrong and what to do to fix it?

Thanks.

 

[John Big Booty]

The problem you are having, is that PBaldy's code is designed to be fired from the form itself. You have not taken into account that you are firing the code externally to the form you wish it to act on, so you need to reference that form explicitly.

See this page for the correct syntax for referencing forms, SubForms and their various properties, controls etc. Bookmark it, it is an invaluable resource.

Try;

Private Sub Command2_Click()
'Begin PBaldy Code Insert--------------------------------------
    Dim rs As Object
    Dim lngBookmark As Long
'End PBaldy Code Insert ---------------------------------------
On Error GoTo Err_Command2_Click

    Dim stDocName As String

    stDocName = "QRY_AddStar"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    
    DoCmd.Close
    
    
'Begin PBaldy Code Insert -------------------------------------
    'set a variable to the current record
    lngBookmark = Forms!form1.MovieID
    'requery the form
    Forms!form1.Requery


    'bring us back to the original record
    Set rs = Forms!form1.RecordsetClone
    rs.FindFirst "MovieID=" & lngBookmark
    Forms!form1.Bookmark = rs.Bookmark
'End PBaldy Code Insert ---------------------------------------

'Begin PBaldy Code Insert -------------------------------------
    Set rs = Nothing
'End PBaldy Code Insert ---------------------------------------

Exit_Command2_Click:

    Exit Sub

Err_Command2_Click:
    MsgBox Err.Description
    Resume Exit_Command2_Click
    
End Sub

You also need to reference the MovieID as that is the record you wish to stay focused on.

 

[oZone]

Thanks for that bookmark, going to read it now.

Thats funny, I tried that too (the "Forms!form1.MovieID parts)exactly how you re-wrote the code.

It doesnt work, I get the following error:
"No current record."

Then the form looses focus on the current movie like before.

Any ideas?

 

[John Big Booty]

Worked fine for me the code was a straight cut and paste from the attached.

 

[oZone]

Hmm... weirdness... I'll try yours. Maybe something got FUBAR'd in mine...?

Thanks.

EDIT:
I just tried yours, I got the same error message after adding a star to a new movie. When you tested it, did you type in a new movie name, like "zzzzzzz" or something, then try to add a new star to the new movie?

Try this and see if you get the same error:
1) Click the Add New Movie button.
2) Enter a new movie name (E.G. zzzzzzzz)
3) Click the "Add a star to this movie" button
4) Select a star from the drop down menu in the "Add a star to this movie" form.
5) Click the Add Star button.

You should see the error message after you do that.... I do... lol!

I can add and delete stars at will in an existing movie, without error messages. But the error message happens when adding a new star while adding a new movie.

 

[oZone]

Naturally I'm not one to give up on things, but this headache of a problem is seriously making me want to toss in the towel on this one...

Am I even putting the code in the rite form?

I've tried everything I can think of to reference the main form MovieID:
Me.frmAddEditMoviesSubform.Form1.MovieID
Forms!frmAddEditMoviesSubform.Form1.MovieID
Forms!Form1.MovieID
Me.Form1.MovieID

etc... still nothing seems to work.

 

[John Big Booty]

To be honest I can't get your form to add a movie in the first place. So I would suggest that you get that feature working before you worry about adding actors. That in a nut shell is why you are getting a "No Current Record" message, as the movie has not been saved to the DB at the stage you are trying to add actors.

 

[oZone]

I can add new movies with this form now, I fixed it. I dont get the error message anymore either.

What happens now is what was happening before; Even with PBaldy's code, the form still looses focus on the current movie after I add a new star to a new movie.

So, I have to navigate back to the movie I just entered (using the combo box) to add more actors. It works, but it's clunky and it'd be nice if someone would help me get PBaldy's code working correctly.

Do I have PBaldy's code in the wrong place or ???

I'll upload my updated DB in a few min.

 

[oZone]

Heres my updated DB.

PBaldy refresh code still not working on frmAddStarToMovie so I removed the code from this form.

Please tell me there's someone out there that can get PBaldy's code to work with my form...

 

[pbaldy]

Try

  Dim stDocName     As String
  Dim rs            As Object
  Dim lngBookmark   As Long

  'set a variable to the current record
  lngBookmark = Forms!frmAddEditMovies.MovieID

  stDocName = "QRY_AddStar"
  DoCmd.OpenQuery stDocName, acNormal, acEdit


  Forms!frmAddEditMovies.Requery
  'bring us back to the original record
  Set rs = Forms!frmAddEditMovies.RecordsetClone
  rs.FindFirst "MovieID = " & lngBookmark
  Forms!frmAddEditMovies.Bookmark = rs.Bookmark
  DoCmd.Close

 

[pbaldy]

BTW, if it wasn't clear, that's the code behind Command2 on frmAddStarToMovie.

 

[oZone]

Thanks for helping Pbaldy!

Unfortunately my first attempt failed... I posted the code above directly into Command2 on frmAddStarToMovie just as you said, but after entering a new movie name ("z"), then adding a star to it, I got this error message:
"Run-time error '3021':
No current record."

Here is the code I have for Command2 in its entirety, with your modification:

Private Sub Command2_Click()
  Dim stDocName     As String
  Dim rs            As Object
  Dim lngBookmark   As Long

  'set a variable to the current record
  lngBookmark = Forms!frmAddEditMovies.MovieID

  stDocName = "QRY_AddStar"
  DoCmd.OpenQuery stDocName, acNormal, acEdit

  Forms!frmAddEditMovies.Requery
  'bring us back to the original record
  Set rs = Forms!frmAddEditMovies.RecordsetClone
  rs.FindFirst "MovieID = " & lngBookmark
  Forms!frmAddEditMovies.Bookmark = rs.Bookmark
  DoCmd.Close

End Sub

Now, after this error message happens, I close it, then go back to the form and I can select the newly added movie from the combo box and it does show the star I added rite before the error happened.

It's just not staying on the new movie I'm entering. After assigning a star, and after the error message, the form still looses focus on the current record and goes back to the first record.

Am I doing something wrong?