Synchronize "After Update" and "Not In List" functions

[Tom]

Hi:

I need some help w/ synchronizing the 2 functions... "AfterUpdate" &
"NotinList".

Currently, I have a main form plus subform. On the main form, I placed an
UNBOUND combo box which contains the AFTERUPDATE event (see below)

***********************************************
Private Sub CboMoveTo_AfterUpdate()

If Not IsNull(Me.cboMoveTo) Then
If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[Position] = """ & Me.cboMoveTo & """"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub
***********************************************

When I select a value (Position) from the combo box, its underlying details
are displayed in the subform (which works great!).


As of now, I can't add value to the combo box (LimitToList = Yes). To fix
that, I added the function below which prompts me to accept to values. If
I click OK, the new value is added to the table (which also works great).

***********************************************
Private Sub cboMoveTo_NotInList(NewData As String, Response As Integer)

Dim MySql As String
Beep

If MsgBox("'" & NewData & "' is currently not an existing Position. " &
vbCrLf _
& "Would you like to add '" & NewData & "' to the menu?",
vbInformation + vbOKCancel, "Info") = vbOK Then
MySql = "Insert into tblPositions (Position) VALUES ('" & NewData &
"')"
CurrentDb.Execute MySql
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me.Position = Null
End If

End Sub
***********************************************


Okay, here's my problem...
- I select an existing Position... which brings up the underlying details
(ok)
- I enter a new value into combo box... I'm prompted to accept the value
(ok)
- Then, however, the line [MsgBox "Not found: filtered?" kicks in (makes
sense, but's a problem)
- Also, the underlying details from the previously selected Position remain
listed

Here's what I'm trying to achieve:
- When accepting the new value in the combo box, I don't want the MsgBox
"Not filtered..." appear
- I also want the details to show NO records at all (since no record details
have been entered for the new Position yet).
- Currently, again, I get the unwanted msg box & I must close/reopen the
form to view 0 records for the new Position value

Does anyone know how I can synchronize the 2 functions?

Thanks in advance,
Tom

 

[Wayne Morgan]

The form has already pulled the records from its recordset before you add
the new value. After adding the data using the combo box's NotInList event,
to have the form search for that data, you'll have to requery the form
first.

Me.Requery
or
Me.NameOfSubformControl.Form.Requery

--
Wayne Morgan
Microsoft Access MVP

Hi:

I need some help w/ synchronizing the 2 functions... "AfterUpdate" &
"NotinList".

Currently, I have a main form plus subform. On the main form, I placed
an
UNBOUND combo box which contains the AFTERUPDATE event (see below)

***********************************************
Private Sub CboMoveTo_AfterUpdate()

If Not IsNull(Me.cboMoveTo) Then
If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[Position] = """ & Me.cboMoveTo & """"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub
***********************************************

When I select a value (Position) from the combo box, its underlying
details
are displayed in the subform (which works great!).


As of now, I can't add value to the combo box (LimitToList = Yes). To fix
that, I added the function below which prompts me to accept to values.
If
I click OK, the new value is added to the table (which also works great).

***********************************************
Private Sub cboMoveTo_NotInList(NewData As String, Response As Integer)

Dim MySql As String
Beep

If MsgBox("'" & NewData & "' is currently not an existing Position. " &
vbCrLf _
& "Would you like to add '" & NewData & "' to the menu?",
vbInformation + vbOKCancel, "Info") = vbOK Then
MySql = "Insert into tblPositions (Position) VALUES ('" & NewData &
"')"
CurrentDb.Execute MySql
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me.Position = Null
End If

End Sub
***********************************************


Okay, here's my problem...
- I select an existing Position... which brings up the underlying details
(ok)
- I enter a new value into combo box... I'm prompted to accept the value
(ok)
- Then, however, the line [MsgBox "Not found: filtered?" kicks in (makes
sense, but's a problem)
- Also, the underlying details from the previously selected Position
remain
listed

Here's what I'm trying to achieve:
- When accepting the new value in the combo box, I don't want the MsgBox
"Not filtered..." appear
- I also want the details to show NO records at all (since no record
details
have been entered for the new Position yet).
- Currently, again, I get the unwanted msg box & I must close/reopen the
form to view 0 records for the new Position value

Does anyone know how I can synchronize the 2 functions?

Thanks in advance,
Tom

 

[Tom]

Wayne:

Thanks for the reply...

I changed it to "Me.cboMoveTo.Form.Requery"... obviously, this isn't correct
since the VBA window pops up and highlights ".Form" in that line.

"cboMoveTo" is the "Name" (Other tab) of the unbound combo box. Or was I
supposed to choose a different control object? Also, do I need to replace
"Form" with the actual formname? If yes, the name of the main form or sub
form?

--
Thanks,
Tom

The form has already pulled the records from its recordset before you add
the new value. After adding the data using the combo box's NotInList
event, to have the form search for that data, you'll have to requery the
form first.

Me.Requery
or
Me.NameOfSubformControl.Form.Requery

--
Wayne Morgan
Microsoft Access MVP

Hi:

I need some help w/ synchronizing the 2 functions... "AfterUpdate" &
"NotinList".

Currently, I have a main form plus subform. On the main form, I placed
an
UNBOUND combo box which contains the AFTERUPDATE event (see below)

***********************************************
Private Sub CboMoveTo_AfterUpdate()

If Not IsNull(Me.cboMoveTo) Then
If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[Position] = """ & Me.cboMoveTo & """"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub
***********************************************

When I select a value (Position) from the combo box, its underlying
details
are displayed in the subform (which works great!).


As of now, I can't add value to the combo box (LimitToList = Yes). To
fix
that, I added the function below which prompts me to accept to values. If
I click OK, the new value is added to the table (which also works great).

***********************************************
Private Sub cboMoveTo_NotInList(NewData As String, Response As Integer)

Dim MySql As String
Beep

If MsgBox("'" & NewData & "' is currently not an existing Position. "
&
vbCrLf _
& "Would you like to add '" & NewData & "' to the menu?",
vbInformation + vbOKCancel, "Info") = vbOK Then
MySql = "Insert into tblPositions (Position) VALUES ('" & NewData &
"')"
CurrentDb.Execute MySql
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me.Position = Null
End If

End Sub
***********************************************


Okay, here's my problem...
- I select an existing Position... which brings up the underlying details
(ok)
- I enter a new value into combo box... I'm prompted to accept the value
(ok)
- Then, however, the line [MsgBox "Not found: filtered?" kicks in (makes
sense, but's a problem)
- Also, the underlying details from the previously selected Position
remain
listed

Here's what I'm trying to achieve:
- When accepting the new value in the combo box, I don't want the MsgBox
"Not filtered..." appear
- I also want the details to show NO records at all (since no record
details
have been entered for the new Position yet).
- Currently, again, I get the unwanted msg box & I must close/reopen the
form to view 0 records for the new Position value

Does anyone know how I can synchronize the 2 functions?

Thanks in advance,
Tom

 

[Wayne Morgan]

The requery would be for the form or subform, not for an individual control
unless that is the only thing you need updated. To requery the form the code
is running on, the command is

Me.Requery

To requery a subform of the form that the code is running on, the command is

Me.ctlNameOfSubformControl.Form.Requery

If you only need to requery one control, that can also be done. The command
for that would be

Me.ctlControlName.Requery
or
Me.ctlNameOfSubformControl.Form.ctlControlName.Requery

Again, the first one is for a control on the form that the code is running
on, the second one is for a control on a subform of the form the code is
running on.

--
Wayne Morgan
MS Access MVP

Wayne:

Thanks for the reply...

I changed it to "Me.cboMoveTo.Form.Requery"... obviously, this isn't
correct since the VBA window pops up and highlights ".Form" in that line.

"cboMoveTo" is the "Name" (Other tab) of the unbound combo box. Or was I
supposed to choose a different control object? Also, do I need to
replace "Form" with the actual formname? If yes, the name of the main
form or sub form?

--
Thanks,
Tom

The form has already pulled the records from its recordset before you add
the new value. After adding the data using the combo box's NotInList
event, to have the form search for that data, you'll have to requery the
form first.

Me.Requery
or
Me.NameOfSubformControl.Form.Requery

--
Wayne Morgan
Microsoft Access MVP

Hi:

I need some help w/ synchronizing the 2 functions... "AfterUpdate" &
"NotinList".

Currently, I have a main form plus subform. On the main form, I placed
an
UNBOUND combo box which contains the AFTERUPDATE event (see below)

***********************************************
Private Sub CboMoveTo_AfterUpdate()

If Not IsNull(Me.cboMoveTo) Then
If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[Position] = """ & Me.cboMoveTo & """"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub
***********************************************

When I select a value (Position) from the combo box, its underlying
details
are displayed in the subform (which works great!).


As of now, I can't add value to the combo box (LimitToList = Yes). To
fix
that, I added the function below which prompts me to accept to values.
If
I click OK, the new value is added to the table (which also works
great).

***********************************************
Private Sub cboMoveTo_NotInList(NewData As String, Response As Integer)

Dim MySql As String
Beep

If MsgBox("'" & NewData & "' is currently not an existing Position. "
&
vbCrLf _
& "Would you like to add '" & NewData & "' to the menu?",
vbInformation + vbOKCancel, "Info") = vbOK Then
MySql = "Insert into tblPositions (Position) VALUES ('" & NewData
&
"')"
CurrentDb.Execute MySql
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me.Position = Null
End If

End Sub
***********************************************


Okay, here's my problem...
- I select an existing Position... which brings up the underlying
details
(ok)
- I enter a new value into combo box... I'm prompted to accept the value
(ok)
- Then, however, the line [MsgBox "Not found: filtered?" kicks in (makes
sense, but's a problem)
- Also, the underlying details from the previously selected Position
remain
listed

Here's what I'm trying to achieve:
- When accepting the new value in the combo box, I don't want the MsgBox
"Not filtered..." appear
- I also want the details to show NO records at all (since no record
details
have been entered for the new Position yet).
- Currently, again, I get the unwanted msg box & I must close/reopen the
form to view 0 records for the new Position value

Does anyone know how I can synchronize the 2 functions?

Thanks in advance,
Tom