notinlist event problem

[bjdobs]

I have a Main Form control setup with a notinlist event ... if I enter a value in the control the event works great and my add to list code works. However a subform control has a result that I want to throw into the Main form control.

I used the Forms!<formname>.<controlname>.value = <somevalue> ... this doesn't trip the notinlist event so the value is not added to the list

is there a way in code to force the notinlist event to occur? Keeping in mind behind the notinlist event is some kind of test to ensure duplicates are not added

 

[TheAceMan1]

How are ya bjdobs . . . . .

[blue]Post the code in you [purple]NotInList[/purple] event . . .[/blue]

See Ya! . . . . . .

 

[bjdobs]

I don't think the code in the event is the issue as the event is not being triggered by the assignment from the subform ( I set a breakpoint on the first statement in the event code and it never gets there)

I have just spent the last 2 hours going through everything I can find on combobox notinlist etc and the only conclusion I can come up with is this event is only triggered by user entry ... so now I am looking for a way to spoof a user entry

notinlist event code
If NewData = Null Then Exit Sub
If NewData = "" Then Exit Sub

Me!somefield.RowSource = Chr(34) & NewData & Chr(34) & ";" & Me!somefield.RowSource

Response = acDataErrAdded

 

[bjdobs]

Uncle!!! I can't find a way to get the notinlist to fire from code ... soooooo ... What I am going to do is use the instr function on the RowSource to check for a "not in list" and then fire the NOTINLIST event function if required from there.

 

[TheAceMan1]

bjdobs . . . . .

As a learned profrssional I asked because the code is very revealing and usually prevents a number of questions. [blue]RowSourceType[/blue] being one of them.

First, [blue]the event is not triggered by nulls or empty strings.[/blue] So those lines of code are not necessary.

You have several options available:
[ol][li]Concatenate the subform control as well:

[blue]   Dim frm As Form, Build As String, DQ As String   

   Set frm = Me![purple][b]YourSubformName[/b][/purple].Form [green]'subForm reference[/green]
   DQ = """" [green]'Double Quotes[/green]
   
   Build = DQ & NewData & DQ & ";"
   Build = DQ & frm![purple][b]subFormControlName[/b][/purple] & DQ & ";" & Build
   Me![purple][b]ComboboxName[/b][/purple].RowSource = Build & Me![purple][b]ComboboxName[/b][/purple].RowSource
   Response = acDataErrAdded[/blue]

[/li]
[li] Call the [purple]NotInList event[/purple] from another routine within the same code module as the event. [blue]Since the event is private, its only available to routines in that module![/blue]. So in the code module of the mainform you would add something like:

[blue]Public Sub NotInListExt(Data As String)
   Call [purple][b]ComboBoxName[/b][/purple]_NotInList(Data, 1)
End Sub[/blue]

To make the call from the subform, it would be:

[blue]Forms![purple][b]MainFormName[/b][/purple].NotInListExt(Me![purple][b]TextBoxName[/b][/purple])[/blue]

[/li][/ol]
[purple]Your Thoughts?[/purple]

See Ya! . . . . . .

 

[bjdobs]

Thanx for the info ... my code snippit was copied from one of those How-to sites so the first two lines are probably along for the ride in alot of code out there ... there is no harm of it being there but your insight is welcome ... I was aware of issues of intermodule calls ... rereading my response I appologise for being careless in how I worded my solution ... I didn't actually call the Event Procedure instead I used a 3 line insert into my subform requery function that does both the similation of the NOTINLIST Event plus runs duplicate code from the Notinlist Event procedure adding to list when required

IF instr(1,Forms!<Mainform>.<control>.RowSource,(CHR(34) & Me.<control>.value & chr(34)) = 0 then
Forms!<MainForm>.<control>.RowSource = CHR(34) & Me.<control>.value & chr(34) & ";" & Forms!<MainForm>.<Control>.RowSource
end if

I do not follow your second Build assignment ... when I view the contents of the Rowsource value in a watch window only the list values are there in the form "value1";"value2"; etc. so Im not following why you are including the control name?

As I stated earlier regardless of the method used (forcing a call to the actual NOTINLIST function, OR similating this call (code duplication yuk)) there does not appear to be a way to trigger the internal (access's) NOTINLIST EVENT (not the Event code) ... I was thinking of using a method of similating keystrokes but that is a kludge that has ugly ramifications if someone is bashing away at the keyboard