Formulate Index Value

[NeoPa]

The combo box is called [SongTitleID] and yes, it's line #18.

It is now clear that the problem is not the code as such - but the order (or timing) of when the code is run.

When you open a new form, the code after the OpenForm runs immediately afterwards. It is not synchronous so it doesn't wait for the form to close before continuing. This means that the requery is done before any changes are made via the form.

What you need to do is to call the Requery from the code in the opened form, as it closes. You can pass the name of the form and / or control to the new form using OpenArgs if necessary.

Does that make sense?

[MNNovice]

What you need to do is to call the Requery from the code in the opened form, as it closes. You can pass the name of the form and / or control to the new form using OpenArgs if necessary.

Does that make sense?

Sorry but this is way over my expertise/understanding of SQL. I applied similar codes (in the same order) to update Artists and Categories and those two combo boxes are working just fine namely, [RecordingArtist ID] and [MusicCategoryID]. Why it does not work for [SongTitleID] - that I don't understand.

Thanks.

[NeoPa]

To answer that I would need to see the other code. I suspect it's different in some way that's not obvious to you. Maybe doesn't use a separate form to enter the new item. I don't know until I get to see the database with the problem in it. Certainly, your code for this I would NOT expect to work for the reasons outlined.

To explain again, more simply if I can, the requery, if it's to have the desired effect, needs to execute after the item is added on the form.

As the requery code is immediately after the code which opens the form, it is run immediately after the form is opened, and not after the item has been added on the form.

Let's have another copy of the database and we'll see what we can discover.

[NeoPa]

I should say I'm not in Wednesday evening and tonight might also be a little tight. I will do what I can if you post the database but it may not get done until Thursday. Obviously I'll do what I can though :)

[MNNovice]

To answer that I would need to see the other code. I suspect it's different in some way that's not obvious to you. Maybe doesn't use a separate form to enter the new item. I don't know until I get to see the database with the problem in it. Certainly, your code for this I would NOT expect to work for the reasons outlined.

To explain again, more simply if I can, the requery, if it's to have the desired effect, needs to execute after the item is added on the form.

As the requery code is immediately after the code which opens the form, it is run immediately after the form is opened, and not after the item has been added on the form.

Let's have another copy of the database and we'll see what we can discover.

Here is what I am trying to achieve.

I opened the form frmCDDetails to add a new CD. The page for Record 584 opened. I entered a CD titled "The Gold Collection Vol 1" by Karen Carpenters. Now I would like to enter the track info for this CD. For example, a track titled: "Close to you" But this song is not listed in tblSongs and therefore does not show up in the drop down list. So in order to enter the track info, I would like to click on [SongTitleID] (located in the subform sfrmCDDetails). But when I double click on this combo box [SongTitleID], I get the error message I explained earlier.

** Edit **

On frmCDDetails, when I double click on [SongTitleID] on sfrmCDDetails to add a new song, I get this error message: "You tried to assign the Null Value to a variable that is not a variant data type". I don't understand what does this mean.

** /Edit **


However, on the same page of this form frmCDDetails, I can add a new Music Category or a new Artist's name by double clicking the combo boxes, [MusicCategoryID] and [RecordingArtist ID] respectively.

Hope this explains my problem. Looking forward to your solutions, whenever you have time.

As always, thank you very much.

[NeoPa]

Database downloaded.

Will update when I get time to look at it.

[NeoPa]

I leave this for two days and then I have to search pages of threads to find it again.

Just to let you know I'm looking at it now. Hope to post something soon.

[MNNovice]

No hurry. Take your time. Let me know if you have any questions.

I will be leaving for Iowa for the weekend. Shall return Sunday. And thank you for your help. Hope you get some time off to enjoy with family and friends.

Take care.

[NeoPa]

Please check your PMs and let me know your response.

[NeoPa]

I've just noticed (after working through some of the code) that line #17 of post #130 includes a parameter (acDialog) which means that my post #136 is not correct. Please disregard it.

[NeoPa]

If IsNull(Me![SongTitleID]) Then
    Me![SongTitleID].Text = ""
Else
    lngSongTitleID = Me![SongTitleID]
    Me![SongTitleID] = Null
End If

This is the code in your procedure where the error occurs (Line #5). I did have a fair look to see if I could see why this crashes when the same (similar) code for Artist doesn't have the same problem, but I couldn't find it easily, and I didn't want to spend too much more time on it as removing line #5 (Me![SongTitleID] = Null) seems to fix the problem. I can't see any reason for that line, but if there is one just tell me. If not, simply lose it. It does work without it as far as I can see.

[NeoPa]

I've found out why you can't delete line #5 :(

I'm struggling to understand why the control [SongTitleID] behaves so differently from the others. I think I will need to get back to this at some other time.

I've checked through the way the different (one working & one not) controls are set up and even back to the queries and tables they come from. While there are some minor differences, I can't see anything which points to why.

[MNNovice]

I've found out why you can't delete line #5 :(

I'm struggling to understand why the control [SongTitleID] behaves so differently from the others. I think I will need to get back to this at some other time.

I've checked through the way the different (one working & one not) controls are set up and even back to the queries and tables they come from. While there are some minor differences, I can't see anything which points to why.

I was able to sort out this problem. I just marked that line (#5 in your posting, #9 in this posting) a comment instead. It seems that took care of the problem. Let me know if this is not the right way to fixing this issue. Here are the revised codes and it's working now. Lots of thanks for your help.

Private Sub SongTitleID_DblClick(Cancel As Integer)
On Error GoTo Err_SongTitleID_DblClick
    Dim lngSongID As Long

    If IsNull(Me![SongTitleID]) Then
        Me![SongTitleID].Text = ""
    Else
        lngSongTitleID = Me![SongTitleID]
        'Me![SongTitleID] = Null
    End If
    DoCmd.OpenForm "frmSongs", , , , , acDialog, "GotoNew"
    Me![SongTitleID].Requery
    If lngSongTitleID <> 0 Then Me![SongTitleID] = lngSongTitleID

Exit_SongTitleID_DblClick:
    Exit Sub

Err_SongTitleID_DblClick:
    MsgBox Err.Description
    Resume Exit_SongTitleID_DblClick
End Sub

[NeoPa]

I tried that first. Unfortunately it doesn't really solve the problem. It works sometimes, but not in other circumstances :(

I would say it's a worthwhile compromise for the moment though. It's better than not working at all :)

[NeoPa]

Please check your PMs and let me know your response.

Did you manage to check your PMs yet?