NOTINLISt problem

[iankerry]

Hi

I have a continuous form that lists dates of film screenings, date time and which film is being screened.

a combo box list all the films (this isn't quite true but see later!) we have on our database.

When the user types in a new film that isnt on the list, a box comes up saying essentially "do you want to add it". If you click yes, then another form is loaded, the FormFilms. All is well so far...

The complication is that we often have several copies of a film, so i have a films table AND a filmcopies table, which are linked (in the filmcopies table there is a FilmID field which relates to the film title). So technically the drop down box mentioned above is a list of film copies, not film titles.

Anyhow, when the form opens to add a new film i run into problems.

1. i can't add a new film title, unless i make the form Dynaset (Inconsistent Updates) and i am not sure of the consequences of this...

2. i cant get what the user has already typed in to be the start of the new record.

3. but even using inconsistent updates, i still have the problem that when i add a new film, i need to get the new FilmID into the Filmcopies table, and i can't do that until i have saved the new record and have been allocated a new FilmID. Is there a way around this?

4. when i go back to the form, i cant seem to requery the combo so that the film is automatically entered!!!!

I have been googling and playing this for ages. Any help really appreciated.

i have attached a cut down version of the forms/tables as it is really hard to explain.

thanks

ian

 

[PaulO]

I think you need to take a step back on this first ...

When a new film (copy) is needed I would perhaps have a button called "add" next to the drop-down list that would call up another FORM accessing the Films table and which would enable you to add a whole new Film record including the important/unique ID ref. Once the new record is added and the Form closed, you will be returned to the original screen, press F9 (refreshes the screen), and the newly-added film should be in the drop-down list.

BTW ... that's one heck of a busy form you have there! I'd be sorely tempted to use some sub-forms to help you view/sort/query your data better but that's perhaps another topic for another day.

 

[iankerry]

​

I think you need to take a step back on this first ...]

ah. am i after something difficult? i was hoping that it would make things much easier to operate.

[When a new film (copy) is needed I would perhaps have a button called "add" next to the drop-down list that would call up another FORM accessing the Films table and which would enable you to add a whole new Film record including the important/unique ID ref. Once the new record is added and the Form closed, you will be returned to the original screen, press F9 (refreshes the screen), and the newly-added film should be in the drop-down list.]​

it isn't even as simple as this - i can't just make a new film record, i have to make a filmcopy record, using the filmID number. I would use a button, but i thought the notinlist would be neater.

BTW ... that's one heck of a busy form you have there! I'd be sorely tempted to use some sub-forms to help you view/sort/query your data better but that's perhaps another topic for another day​

i have taken quite a lot off it already! The users seem to like it - i did offer to make it simpler. guess it is what you are used too.

thanks for you imput!

 

[lcook1974]

I got this code from Bob Larson (smart guy who helps a lot!)

This may work for you if you tweak it a little

Private Sub cmbLookupTitle_NotInList(NewData As String, Response As Integer)
    If MsgBox("This is not in the list.  Do you wish to add it?", vbQuestion + vbYesNo, "Add " & NewData & "?") = vbYes Then
        DoCmd.OpenForm "frmObjectiveTitle", acNormal, , , acFormAdd, acDialog, NewData
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
        Me.cmbLookupTitle.Undo
    End If
End Sub

LWC

 

[iankerry]

Hi

Thanks for that I will give it a go - not that i understand it too much but i am sure all will become clear!

Ian