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

[pbaldy]

I'll have to look at it later, as I've got to head to a meeting. It was working perfectly for me, but I'm not sure I tested from a new movie.

 

[pbaldy]

Never mind, I know why it errors on a new record. The source of the form is a query that refers to the combo above. On a new record, there is no value in that combo, so when you requery, you have an empty recordset, thus no record to go to.

 

[oZone]

I see. So, in my situation, is there no way to make it work?

I've got to go to a meeting as well. I'll be back in a few hours.

 

[pbaldy]

There's always a way to make it work. Keeping with your current structure, add this line before the requery:

Forms!frmAddEditMovies.Combo0 = Forms!frmAddEditMovies.MovieID

 

[oZone]

My hats off to you sir! You saved me from my coding nightmare! Thank you!

Your solution works brilliantly

Thank you for your help, now I can move on to other forms in my DB

You rock PBaldy!

 

[pbaldy]

LOL! I actually am rocking right now. I'm cooking pasta sauce, got a Guns and Roses dvd playing, with Tina Turner up next.

Glad it worked for you.

 

[oZone]

It's working great, but the fromAddStarsToMovie will only allow me to add one star at a time to the movie. If I add two or more, the update message says "You are about to append 0 rows." and doesnt add any stars to the movie. Normally it says "You are about to append 1 row." if adding just one star.

If I have 5 stars to add to a movie, I have to open the form, add one star, then close the form, 5 times. It seems a little user un-friendly...

How would I make it so this form will add multiple stars to a movie at once? I can add multiple new stars to the database, I just cant add multiple stars to a movie...

Here is the code for the "Add Star" button in this form (if it helps):

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 = "qryAddStar"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

    Forms!frmAddEditMovies.Combo0 = Forms!frmAddEditMovies.MovieID
    
    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

Attached DB below.

Also, is there a way to suppress the Append and Update Row confirmation messages that happen after clicking the Add Star button?

As it is now, when I click the "Add Star" button (in frmAddStarToMovie) I get a prompt asking me if I'm sure I want to run an Append query to update my table, then after clicking OK I get another prompt asking if I'm sure I want to Update Rows in my table.

Well, I'm sure I want to suppress these annoying messages... lol

I'd like to make it so the user pushes the Add Star button, and the stars are added without having to click two "OK" prompts as well.

 

[pbaldy]

I'd use a multiselect listbox to add multiple stars. I have a little demo here:

http://www.baldyweb.com/MultiselectAppend.htm

To get rid of the warnings, either use

CurrentDb.Execute

instead of OpenQuery, or

DoCmd.SetWarnings False
DoCmd.OpenQuery...
DoCmd.SetWarnings True

Execute is preferred, but will error if the query contains a form reference, which I'm guessing yours does.

 

[oZone]

Awesome, I'll try this now and post back with the results.

I think mine does have a form reference, but I'll try all the methods you suggested.

Thanks again for your help!

You still rock!

 

[oZone]

Well, I'm trying, but I really dont know what I'm doing. I dont know VB, but I'm doing the best I can to learn.

I tried inserting the code in your multiselect listbox example into the "Add Star" buttons OnClick Event in the VB editor, but I dont think I did it rite because it's not working.

I created a list box called lstAddStars and a text box called txtOtherValue (because I'm not clear on what this field does, or how this all works lol)

Maybe this is a little too over my head rite now. Could you show me what code to add where to get it to work?

Here is the OnClick Event code for my AddStars button:

rivate Sub Command2_Click()

'Start PBaldy code insert
Set ctl = Me.lstAddStars
For Each varItem In ctl.ItemsSelected
  rs.AddNew
  rs!StarID = ctl.ItemData(varItem)
  rs!OtherValue = Me.txtOtherValue
  rs.Update
Next varItem
'End PBaldy code insert

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

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

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

    Forms!frmAddEditMovies.Combo0 = Forms!frmAddEditMovies.MovieID
    
    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

 

[oZone]

To get rid of the warnings, either use

CurrentDb.Execute

instead of OpenQuery, or

DoCmd.SetWarnings False
DoCmd.OpenQuery...
DoCmd.SetWarnings True

I tried OpenQuery, it produced errors like you suspected.

However, DoCmd.SetWarnings False and DoCmd.SetWarnings True worked brilliantly! Thanks!