Access noob, subform help. Error msgs, not allowing edits, & not updating fields...?

[oZone]

Access noob, subform help. Error msgs, not allowing edits, & not updating fields...?

I'm working on a home movies database and created a form (MovieDetails) that should allow the user to either edit existing movie information, or add new movies to the database, but the subform is giving me error messages.

When creating new movie entries;
The form allows me to enter data in all other fields, but when I navigate to the subform and select an actor (from a combo box) I get this error message:

Field cannot be updated

I have no idea why...

When editing an existing movie;
When I navigate to the subform and change an existing actor to a different one using the combo box, I get this error message:

The changes you requested to the database were not successfull because they would create duplicate values in the index, primary key, or relationship... etc

A PK error, I know, but I dont know why...

Also, while still editing an existing movie, when I try to add a new actor to the database by typing his/her name into an empty combo box field in the subform, I get this error message:

! Field cannot be updated

and again, I have no idea why...

I should note that after getting this error message (while adding a new actor to an existing movie), and dismissing it, I continued to enter a new actor name and when done, checked the Stars table and sure enough, the new actor was added to the database and assigned a new StarID autonumber. However, even though the Stars table was updated with the new actor, the related StarsToMovies table was not updated with this new info, which it should've been.

Ideally, after adding a new actor to the database using this form, the Stars and related StarsToMovies table should both be updated. In the StarsToMovies table the actors name, along with his/her new StarID and MovieID should be added, but it isnt.

If you check out my sample DB, look for the actor named 9999. It is the test actor I entered through the form. You'll see that it appears in the Stars table, with an assigned StarID (85), but not at all in the StarsToMovies table. I added this actor to the movie A.I. Artificial Intelligence So it shoud've been assigned a MovieID of "1".


Could someone have a look and tell me whats wrong? I've spent hours trying to diagnose it but again, i'm still a noob!

Thanks!

 

[John Big Booty]

Re: Access noob, subform help. Error msgs, not allowing edits, & not updating fields.

It looks like the problem lies in the set up of your actors table and the Combo on your SubForm.

I couldn't quite work out what was going on with that combo so I deleted it and reinstated it. It now works fine.

Additionally in your actors to movies table you only need to store the ActorID and MovieID. I took the liberty of deleting the actor name field. Why store both the ActorID and the Actor name that's what normalizing is all about

 

[oZone]

Re: Access noob, subform help. Error msgs, not allowing edits, & not updating fields.

Good point about the actor names. I became so confuzzled with my problem that I overlooked that

Your awesome! Thanks! The form works MUCH better, but I did notice another bug in it still.

The user should be able to add a new actor to the database by entering his/her name in the subform combo box, but when I enter a new name it gives me this error message:

The text you entered isnt an item in the list.
Select an item from the list, or enter text that matches one of the listed items.

Any ideas? I'll start looking into it too, but you seem to be able to solve issues at WARP speed, when I'm still stuck in 1st gear... LOL

 

[John Big Booty]

Re: Access noob, subform help. Error msgs, not allowing edits, & not updating fields.

What you will need to do is set up a routine to open a data entry form, linked to your Actors table, that opens on double click on the combo. Then requery the combo when the data entry form is closed. Have a look at the Contact management DB available the templates in Access.

 

[oZone]

Re: Access noob, subform help. Error msgs, not allowing edits, & not updating fields.

Thanks for the advice. It seems to be more technical than I can handle being the Access noob that I am... There isn't an easier way to do it?

I'll research your suggestion for sure, I just didn't think it would be that complicated.

 

[John Big Booty]

Re: Access noob, subform help. Error msgs, not allowing edits, & not updating fields.

It's all really quite straight forward. If you have a look at the template DB I suggested, and go through the on double click event step by step you shouldn't have too much trouble. Give it a go and report back

The important part is to create your data entry form straight up, then start looking at the code.

 

[John Big Booty]

Re: Access noob, subform help. Error msgs, not allowing edits, & not updating fields.

OK I'll help you out. In the following code replace all occurrences of Combo7 with the name of your ComboBox and paste it into the on Double Click event of your combo. Also replace FRM_YourFormName with the name of your data entry form.

On Error GoTo Err_Combo7_DblClick
    Dim lngCombo7 As Long

    If IsNull(Me![Combo7]) Then
   
    Else
        lngCombo7 = Me![Combo7]
        Me![Combo7] = Null
    End If
    DoCmd.OpenForm "FRM_YourFormName", , , , , acDialog, "GotoNew"
    Me![Combo7].Requery
    
     Forms![frm_wine]![Combo7].Requery
    If lngCombo7 <> 0 Then Me![Combo7] = lngCombo7

Exit_Combo7_DblClick:
    Exit Sub

Err_Combo7_DblClick:
    MsgBox Err.Description
    Resume Exit_Combo7_DblClick

Now in the on load event of your data entry form paste the following code;

If OpenArgs = "GotoNew" Then
        DoCmd.GoToRecord acDataForm, "FRM_YourFormName", acNewRec
End If

Having replace FRM_YourFormName with your form name

and for the sake of completeness paste the following code into the ComboBox's Not In List event

MsgBox "Double-click this field to add an entry to the list."
    Response = acDataErrContinue

Simple really

 

[oZone]

Re: Access noob, subform help. Error msgs, not allowing edits, & not updating fields.

Thanks I was just getting ready to go read that link you provided too. I'll still read up on it, but I'll also attempt this code transplant and see where it gets me.

Really, thanks allot for the help John. You're a real sport

 

[oZone]

Re: Access noob, subform help. Error msgs, not allowing edits, & not updating fields.

I went into the VB code editor and added the suggested info but I've tried several times and cant seem to get it to work.

When you say to

replace FRM_YourFormName with the name of your data entry form

Do you mean the name of the main form or the subform? I tried it both ways, frmMovieDetails and frmMovieDetailsSubform, but no go.

The closest I got was when I double clicked on the combo box field after typing in a new actor name. I didnt get a message saying to double click like the code suggests, but after I did double click the combo field a small form popped up containing a scrollable list of ALL the actors in my database.
The only bad thing was that the "X" to close the window was grayed out and it wouldnt let me close it. I couldnt click on anything else either so I was forced to do the three finger salute (Ctrl+Alt+Del) and terminate Access by way of the Task Manager.

My combo box is named Combo11, here is my resulting code for assuming the form name was supposed to be the subform, not main form name:

Private Sub Combo11_DblClick(Cancel As Integer)
On Error GoTo Err_Combo11_DblClick
    Dim lngCombo11 As Long

    If IsNull(Me![Combo11]) Then
   
    Else
        lngCombo11 = Me![Combo11]
        Me![Combo11] = Null
    End If
    DoCmd.OpenForm "frmMovieDetailsSubform", , , , , acDialog, "GotoNew"
    Me![Combo11].Requery
    
     Forms![frm_wine]![Combo11].Requery
    If lngCombo11 <> 0 Then Me![Combo11] = lngCombo11

Exit_Combo11_DblClick:
    Exit Sub

Err_Combo11_DblClick:
    MsgBox Err.Description
    Resume Exit_Combo11_DblClick

End Sub

By the way, at one point I got an error message saying it couldnt find "frm_wine"???

I entered the above code into the double click event for Combo11, then entered the following into the On Load Event of the subform:

Private Sub Form_Load()
If OpenArgs = "GotoNew" Then
        DoCmd.GoToRecord acDataForm, "frmMovieDetailsSubform", acNewRec
End If
End Sub

Then I added the following code into the Combo11 Not in List event

Private Sub Combo11_NotInList(NewData As String, Response As Integer)
MsgBox "Double-click this field to add an entry to the list."
    Response = acDataErrContinue
End Sub

I've got to be doing something wrong because it doesnt work.

I attached my modified DB so you could see what I've done.

As it stands now, it does pop up the "Double click to add" message, but seems to be stuck in some sort of unrecoverable loop and it wont let me close the pop-up window...

 

[John Big Booty]

Re: Access noob, subform help. Error msgs, not allowing edits, & not updating fields.

Sorry I was a little (very) vague.

You will need to create a new form that is bound to your Actors table, lets call it FRM_Actors for arguments sake. This is the form that you need to open in the On Double Click event, it is also the form into which you need to load the On Load event code.

Your on Double Click code should now look like this;

Private Sub Combo11_DblClick(Cancel As Integer)
On Error GoTo Err_Combo11_DblClick
    Dim lngCombo11 As Long

    If IsNull(Me![Combo11]) Then
   
    Else
        lngCombo11 = Me![Combo11]
        Me![Combo11] = Null
    End If
    DoCmd.OpenForm "[B]FRM_Actors[/B]", , , , , acDialog, "GotoNew"
    Me![Combo11].Requery
    

Exit_Combo11_DblClick:
    Exit Sub

Err_Combo11_DblClick:
    MsgBox Err.Description
    Resume Exit_Combo11_DblClick

End Sub

there was a couple of lines of extraneous code that I had missed when I was cleaning it up it should now work.

The On Not In List code works fine, try typing a name into the combo, that you know is not in the list and then tab to the next field you should get a pop up message box telling you to double click to add it to the list.

Once again my appolgies for not being as clear as I should have been.

 

[John Big Booty]

Re: Access noob, subform help. Error msgs, not allowing edits, & not updating fields.

You'll also need to change the form name for the on load event code;

Private Sub Form_Load()
If OpenArgs = "GotoNew" Then
        DoCmd.GoToRecord acDataForm, "[B]FRM_Actors[/B]", acNewRec
End If
End Sub

 

[oZone]

Re: Access noob, subform help. Error msgs, not allowing edits, & not updating fields.

Okay, thanks for the clarification. Getting late here, time to turn in. I'll try this first thing in the morning, thanks again!!!

 

[oZone]

Re: Access noob, subform help. Error msgs, not allowing edits, & not updating fields.

I got it to work, but this method of adding a new actor to the database seems sort of "clunky".

Let me explain: If the user enters a new star name, the user then presses tab to go to the next record and a pop-up says to double click to add the star to the database. So the user double clicks and the new "actors" form pops up, but the field name is blank (the star name the user typed into the form wasnt populated to the pop-up windows actor field...), so the user has to then enter the same actor name a second time, close the pop up window, then the user has to select the newly added actor from the drop down list in the subform... all just to add a new star to a movie...

This seems like a very redundantly redundant process that is inefficiently inefficient LOL.

Yes it works, but I need to develop something that is easy to use.

Ideally, the user would enter a new actor name and press tab to go to the next field. That "tabbing" action would enter the new actor into the database. Everything else would happen in the background, with maybe a message box pop-up saying something like "This actor was added to database successfully".

Is there a way to modify this form to do this?

 

[John Big Booty]

Re: Access noob, subform help. Error msgs, not allowing edits, & not updating fields.

I sorry I'm not aware of any other method. If you do mange to find an alternate method please be sure to post it here.

 

[oZone]

Re: Access noob, subform help. Error msgs, not allowing edits, & not updating fields.

I found an alternate way of adding a star, I just used a separate form (the new actors form I created during your tutorial). I re-purposed it and linked it to a command button located in the main form. When the user activates that button, the new Add Actors form pops up and allows the user to do just that.

Granted, after they've entered the new actor the user then has to select that actor from the actors lists in the subform but it feels less complicated for the end user I think.

Still, I like what you've showed me John and will be thinking about applying this technique elsewhere in my database.

Thanks for all your help!

 

[John Big Booty]

Re: Access noob, subform help. Error msgs, not allowing edits, & not updating fields.

You could add a procedure to your Add Actor form that checks the OpenArgs on the On Close event and if they = "GotNew" it could assign the ActorID of the last actor added to the combo, that would get you part way to your goal.