Combo Box Updates, but Form/Data Doesn't

[NFL]

Hello:

I have a combobox set in place to search for records in my current form
called Courses and it works great.

Private Sub Combo65_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset
Me.Combo65.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClassID] = """ & Me.Combo65 & """"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.Combo65 = Null
End Sub

When I want to create a new class, I have a button which will take me to
another form to add the information.

Private Sub CreateNewClass_Click()

DoCmd.OpenForm "CreateNewCourseFm", windowmode:=acDialog

End Sub

When I close the CreateNewClassFm it will take be back to the Courses form.
The new class will show up in the combo box. The problem is that the class
will show up in the combo box, but it won't let me select it unless I close
and reopen the form.

Thank you for your help!

 

[Ken Snell MVP]

Do you have code that requeries the Combo65 control when the
CreateNewCourseFm closes? You need to do that.

 

[NFL]

How would you write the command to requery/refresh data to another form?

The Combo65 does reflect the update though. The problem is I could not
select the new record in Combo65 box until I close the form and reopen it.

I hope that makes sense.

Thank you!

Do you have code that requeries the Combo65 control when the
CreateNewCourseFm closes? You need to do that.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hello:

I have a combobox set in place to search for records in my current form
called Courses and it works great.

Private Sub Combo65_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset
Me.Combo65.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClassID] = """ & Me.Combo65 & """"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.Combo65 = Null
End Sub

When I want to create a new class, I have a button which will take me to
another form to add the information.

Private Sub CreateNewClass_Click()

DoCmd.OpenForm "CreateNewCourseFm", windowmode:=acDialog

End Sub

When I close the CreateNewClassFm it will take be back to the Courses
form.
The new class will show up in the combo box. The problem is that the
class
will show up in the combo box, but it won't let me select it unless I
close
and reopen the form.

Thank you for your help!

 

[Ken Snell MVP]

If the newly added item is showing in the dropdown list of Combo65, then you
are requerying that combo box already.

When you say the form will not let you select that new item in the dropdown
list, what happens when you click on it? Does the dropdown list go away but
you don't see the new item in the "textbox" part of the combo box? Do you
get some type of error? Or are you saying that your form does not "move" to
the record represented by that new item.

Your current code for the AfterUpdate event of Combo65 sets the value of the
combobox back to Null after the code moves the form to the record
represented by the newly added item. If the form's recordset does not
include a record for that newly added item, the form will stay where it is
and the combobox will have nothing in it.

Assuming that your form should have a record that matches the newly added
item, then you need to requery the form when the "new item" form closes.
That is best done in the Close event of the "new item" form:

Private Sub Form_Close()
Forms!Courses.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

How would you write the command to requery/refresh data to another form?

The Combo65 does reflect the update though. The problem is I could not
select the new record in Combo65 box until I close the form and reopen it.

I hope that makes sense.

Thank you!

Do you have code that requeries the Combo65 control when the
CreateNewCourseFm closes? You need to do that.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hello:

I have a combobox set in place to search for records in my current form
called Courses and it works great.

Private Sub Combo65_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset
Me.Combo65.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClassID] = """ & Me.Combo65 & """"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.Combo65 = Null
End Sub

When I want to create a new class, I have a button which will take me
to
another form to add the information.

Private Sub CreateNewClass_Click()

DoCmd.OpenForm "CreateNewCourseFm", windowmode:=acDialog

End Sub

When I close the CreateNewClassFm it will take be back to the Courses
form.
The new class will show up in the combo box. The problem is that the
class
will show up in the combo box, but it won't let me select it unless I
close
and reopen the form.

Thank you for your help!

 

[NFL]

I'm using MS Access 2003 and sorry to say it is not working.
I'm not getting "errors" when I select Combo65.
The dropdown list does go away if you select the new record and responds
like nothing was selected.
The form does not move to the record respresented by that new item either.
The AfterUpdate event of Combo65 is set to Null at the end of the command,
because once the record is selected, the form moves to the record selected
and the combo box is cleared. I did temporarily removed that code and it
didn't matter if the Null was there or not.
I did enter a the code as suggested..

Private Sub Form_Close()
Forms!Courses.Requery

Thank you,

If the newly added item is showing in the dropdown list of Combo65, then you
are requerying that combo box already.

When you say the form will not let you select that new item in the dropdown
list, what happens when you click on it? Does the dropdown list go away but
you don't see the new item in the "textbox" part of the combo box? Do you
get some type of error? Or are you saying that your form does not "move" to
the record represented by that new item.

Your current code for the AfterUpdate event of Combo65 sets the value of the
combobox back to Null after the code moves the form to the record
represented by the newly added item. If the form's recordset does not
include a record for that newly added item, the form will stay where it is
and the combobox will have nothing in it.

Assuming that your form should have a record that matches the newly added
item, then you need to requery the form when the "new item" form closes.
That is best done in the Close event of the "new item" form:

Private Sub Form_Close()
Forms!Courses.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

How would you write the command to requery/refresh data to another form?

The Combo65 does reflect the update though. The problem is I could not
select the new record in Combo65 box until I close the form and reopen it.

I hope that makes sense.

Thank you!

Do you have code that requeries the Combo65 control when the
CreateNewCourseFm closes? You need to do that.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Hello:

I have a combobox set in place to search for records in my current form
called Courses and it works great.

Private Sub Combo65_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset
Me.Combo65.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClassID] = """ & Me.Combo65 & """"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.Combo65 = Null
End Sub

When I want to create a new class, I have a button which will take me
to
another form to add the information.

Private Sub CreateNewClass_Click()

DoCmd.OpenForm "CreateNewCourseFm", windowmode:=acDialog

End Sub

When I close the CreateNewClassFm it will take be back to the Courses
form.
The new class will show up in the combo box. The problem is that the
class
will show up in the combo box, but it won't let me select it unless I
close
and reopen the form.

Thank you for your help!

 

[Ken Snell [MVP]]

Let's try an experiment. Change your combobox's AfterUpdate code to this:

Private Sub Combo65_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset
Me.Combo65.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClassID] = """ & Me.Combo65 & """"
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Me.Combo65 = Null
Else
MsgBox "Record not found!"
End If
End Sub

Try your form again. Do you get the message popup about record not found?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I'm using MS Access 2003 and sorry to say it is not working.
I'm not getting "errors" when I select Combo65.
The dropdown list does go away if you select the new record and responds
like nothing was selected.
The form does not move to the record respresented by that new item either.
The AfterUpdate event of Combo65 is set to Null at the end of the command,
because once the record is selected, the form moves to the record selected
and the combo box is cleared. I did temporarily removed that code and it
didn't matter if the Null was there or not.
I did enter a the code as suggested..

Private Sub Form_Close()
Forms!Courses.Requery

Thank you,

If the newly added item is showing in the dropdown list of Combo65, then
you
are requerying that combo box already.

When you say the form will not let you select that new item in the
dropdown
list, what happens when you click on it? Does the dropdown list go away
but
you don't see the new item in the "textbox" part of the combo box? Do you
get some type of error? Or are you saying that your form does not "move"
to
the record represented by that new item.

Your current code for the AfterUpdate event of Combo65 sets the value of
the
combobox back to Null after the code moves the form to the record
represented by the newly added item. If the form's recordset does not
include a record for that newly added item, the form will stay where it
is
and the combobox will have nothing in it.

Assuming that your form should have a record that matches the newly added
item, then you need to requery the form when the "new item" form closes.
That is best done in the Close event of the "new item" form:

Private Sub Form_Close()
Forms!Courses.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

How would you write the command to requery/refresh data to another
form?

The Combo65 does reflect the update though. The problem is I could
not
select the new record in Combo65 box until I close the form and reopen
it.

I hope that makes sense.

Thank you!

:

Do you have code that requeries the Combo65 control when the
CreateNewCourseFm closes? You need to do that.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Hello:

I have a combobox set in place to search for records in my current
form
called Courses and it works great.

Private Sub Combo65_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset
Me.Combo65.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClassID] = """ & Me.Combo65 & """"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.Combo65 = Null
End Sub

When I want to create a new class, I have a button which will take
me
to
another form to add the information.

Private Sub CreateNewClass_Click()

DoCmd.OpenForm "CreateNewCourseFm", windowmode:=acDialog

End Sub

When I close the CreateNewClassFm it will take be back to the
Courses
form.
The new class will show up in the combo box. The problem is that
the
class
will show up in the combo box, but it won't let me select it unless
I
close
and reopen the form.

Thank you for your help!

 

[Ken Snell MVP]

Let's try an experiment. Change your combobox's AfterUpdate code to this:

Private Sub Combo65_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset
Me.Combo65.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClassID] = """ & Me.Combo65 & """"
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Me.Combo65 = Null
Else
MsgBox "Record not found!"
End If
End Sub

Try your form again. Do you get the message popup about record not found?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I'm using MS Access 2003 and sorry to say it is not working.
I'm not getting "errors" when I select Combo65.
The dropdown list does go away if you select the new record and responds
like nothing was selected.
The form does not move to the record respresented by that new item either.
The AfterUpdate event of Combo65 is set to Null at the end of the command,
because once the record is selected, the form moves to the record selected
and the combo box is cleared. I did temporarily removed that code and it
didn't matter if the Null was there or not.
I did enter a the code as suggested..

Private Sub Form_Close()
Forms!Courses.Requery

Thank you,

If the newly added item is showing in the dropdown list of Combo65, then
you
are requerying that combo box already.

When you say the form will not let you select that new item in the
dropdown
list, what happens when you click on it? Does the dropdown list go away
but
you don't see the new item in the "textbox" part of the combo box? Do you
get some type of error? Or are you saying that your form does not "move"
to
the record represented by that new item.

Your current code for the AfterUpdate event of Combo65 sets the value of
the
combobox back to Null after the code moves the form to the record
represented by the newly added item. If the form's recordset does not
include a record for that newly added item, the form will stay where it
is
and the combobox will have nothing in it.

Assuming that your form should have a record that matches the newly added
item, then you need to requery the form when the "new item" form closes.
That is best done in the Close event of the "new item" form:

Private Sub Form_Close()
Forms!Courses.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

How would you write the command to requery/refresh data to another
form?

The Combo65 does reflect the update though. The problem is I could
not
select the new record in Combo65 box until I close the form and reopen
it.

I hope that makes sense.

Thank you!

:

Do you have code that requeries the Combo65 control when the
CreateNewCourseFm closes? You need to do that.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Hello:

I have a combobox set in place to search for records in my current
form
called Courses and it works great.

Private Sub Combo65_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset
Me.Combo65.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClassID] = """ & Me.Combo65 & """"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.Combo65 = Null
End Sub

When I want to create a new class, I have a button which will take
me
to
another form to add the information.

Private Sub CreateNewClass_Click()

DoCmd.OpenForm "CreateNewCourseFm", windowmode:=acDialog

End Sub

When I close the CreateNewClassFm it will take be back to the
Courses
form.
The new class will show up in the combo box. The problem is that
the
class
will show up in the combo box, but it won't let me select it unless
I
close
and reopen the form.

Thank you for your help!

 

[NFL]

No luck ... :-(

Question: When I press the command button to open a form, is there a way to
write a procedure to close the current form as I update the table using the
other form and then reverse the process? Here is what I have so far on the
main form..

Private Sub Combo65_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset
Me.Combo65.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClassID] = """ & Me.Combo65 & """"
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Me.Combo65 = Null
Else
MsgBox "Record not found!"
End If
End Sub

Private Sub Form_Close()
Forms!Courses.Requery
End Sub

Private Sub Form_Current()
Me.Combo65.Requery
End Sub

Private Sub CreateNewClass_Click()

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "CreateNewCourseFm"

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd
Me.Combo65.Requery
End Sub

Thank you!

Let's try an experiment. Change your combobox's AfterUpdate code to this:

Private Sub Combo65_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset
Me.Combo65.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClassID] = """ & Me.Combo65 & """"
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Me.Combo65 = Null
Else
MsgBox "Record not found!"
End If
End Sub

Try your form again. Do you get the message popup about record not found?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I'm using MS Access 2003 and sorry to say it is not working.
I'm not getting "errors" when I select Combo65.
The dropdown list does go away if you select the new record and responds
like nothing was selected.
The form does not move to the record respresented by that new item either.
The AfterUpdate event of Combo65 is set to Null at the end of the command,
because once the record is selected, the form moves to the record selected
and the combo box is cleared. I did temporarily removed that code and it
didn't matter if the Null was there or not.
I did enter a the code as suggested..

Private Sub Form_Close()
Forms!Courses.Requery

Thank you,

If the newly added item is showing in the dropdown list of Combo65, then
you
are requerying that combo box already.

When you say the form will not let you select that new item in the
dropdown
list, what happens when you click on it? Does the dropdown list go away
but
you don't see the new item in the "textbox" part of the combo box? Do you
get some type of error? Or are you saying that your form does not "move"
to
the record represented by that new item.

Your current code for the AfterUpdate event of Combo65 sets the value of
the
combobox back to Null after the code moves the form to the record
represented by the newly added item. If the form's recordset does not
include a record for that newly added item, the form will stay where it
is
and the combobox will have nothing in it.

Assuming that your form should have a record that matches the newly added
item, then you need to requery the form when the "new item" form closes.
That is best done in the Close event of the "new item" form:

Private Sub Form_Close()
Forms!Courses.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




How would you write the command to requery/refresh data to another
form?

The Combo65 does reflect the update though. The problem is I could
not
select the new record in Combo65 box until I close the form and reopen
it.

I hope that makes sense.

Thank you!

:

Do you have code that requeries the Combo65 control when the
CreateNewCourseFm closes? You need to do that.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Hello:

I have a combobox set in place to search for records in my current
form
called Courses and it works great.

Private Sub Combo65_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset
Me.Combo65.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClassID] = """ & Me.Combo65 & """"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.Combo65 = Null
End Sub

When I want to create a new class, I have a button which will take
me
to
another form to add the information.

Private Sub CreateNewClass_Click()

DoCmd.OpenForm "CreateNewCourseFm", windowmode:=acDialog

End Sub

When I close the CreateNewClassFm it will take be back to the
Courses
form.
The new class will show up in the combo box. The problem is that
the
class
will show up in the combo box, but it won't let me select it unless
I
close
and reopen the form.

Thank you for your help!

 

[Ken Snell [MVP]]

Before we move on, tell me what the result of the new code was when it ran.
You should have either had the form move to the desired record or you should
have gotten the message box saying that the record was not found. Which
occurred?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

No luck ... :-(

Question: When I press the command button to open a form, is there a way
to
write a procedure to close the current form as I update the table using
the
other form and then reverse the process? Here is what I have so far on
the
main form..

Private Sub Combo65_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset
Me.Combo65.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClassID] = """ & Me.Combo65 & """"
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Me.Combo65 = Null
Else
MsgBox "Record not found!"
End If
End Sub

Private Sub Form_Close()
Forms!Courses.Requery
End Sub

Private Sub Form_Current()
Me.Combo65.Requery
End Sub

Private Sub CreateNewClass_Click()

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "CreateNewCourseFm"

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd
Me.Combo65.Requery
End Sub

Thank you!

Let's try an experiment. Change your combobox's AfterUpdate code to this:

Private Sub Combo65_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset
Me.Combo65.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClassID] = """ & Me.Combo65 & """"
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Me.Combo65 = Null
Else
MsgBox "Record not found!"
End If
End Sub

Try your form again. Do you get the message popup about record not found?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I'm using MS Access 2003 and sorry to say it is not working.
I'm not getting "errors" when I select Combo65.
The dropdown list does go away if you select the new record and
responds
like nothing was selected.
The form does not move to the record respresented by that new item
either.
The AfterUpdate event of Combo65 is set to Null at the end of the
command,
because once the record is selected, the form moves to the record
selected
and the combo box is cleared. I did temporarily removed that code and
it
didn't matter if the Null was there or not.
I did enter a the code as suggested..

Private Sub Form_Close()
Forms!Courses.Requery

Thank you,

:

If the newly added item is showing in the dropdown list of Combo65,
then
you
are requerying that combo box already.

When you say the form will not let you select that new item in the
dropdown
list, what happens when you click on it? Does the dropdown list go
away
but
you don't see the new item in the "textbox" part of the combo box? Do
you
get some type of error? Or are you saying that your form does not
"move"
to
the record represented by that new item.

Your current code for the AfterUpdate event of Combo65 sets the value
of
the
combobox back to Null after the code moves the form to the record
represented by the newly added item. If the form's recordset does not
include a record for that newly added item, the form will stay where
it
is
and the combobox will have nothing in it.

Assuming that your form should have a record that matches the newly
added
item, then you need to requery the form when the "new item" form
closes.
That is best done in the Close event of the "new item" form:

Private Sub Form_Close()
Forms!Courses.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




How would you write the command to requery/refresh data to another
form?

The Combo65 does reflect the update though. The problem is I could
not
select the new record in Combo65 box until I close the form and
reopen
it.

I hope that makes sense.

Thank you!

:

Do you have code that requeries the Combo65 control when the
CreateNewCourseFm closes? You need to do that.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Hello:

I have a combobox set in place to search for records in my
current
form
called Courses and it works great.

Private Sub Combo65_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset
Me.Combo65.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClassID] = """ & Me.Combo65 & """"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.Combo65 = Null
End Sub

When I want to create a new class, I have a button which will
take
me
to
another form to add the information.

Private Sub CreateNewClass_Click()

DoCmd.OpenForm "CreateNewCourseFm", windowmode:=acDialog

End Sub

When I close the CreateNewClassFm it will take be back to the
Courses
form.
The new class will show up in the combo box. The problem is that
the
class
will show up in the combo box, but it won't let me select it
unless
I
close
and reopen the form.

Thank you for your help!

 

[NFL]

I wish I tell you that it either moved to the record or received a msg. The
only thing I could tell you is when I close the form and reopen it, the
desired record would show up and move to that record....

Before we move on, tell me what the result of the new code was when it ran.
You should have either had the form move to the desired record or you should
have gotten the message box saying that the record was not found. Which
occurred?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

No luck ... :-(

Question: When I press the command button to open a form, is there a way
to
write a procedure to close the current form as I update the table using
the
other form and then reverse the process? Here is what I have so far on
the
main form..

Private Sub Combo65_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset
Me.Combo65.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClassID] = """ & Me.Combo65 & """"
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Me.Combo65 = Null
Else
MsgBox "Record not found!"
End If
End Sub

Private Sub Form_Close()
Forms!Courses.Requery
End Sub

Private Sub Form_Current()
Me.Combo65.Requery
End Sub

Private Sub CreateNewClass_Click()

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "CreateNewCourseFm"

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd
Me.Combo65.Requery
End Sub

Thank you!

Let's try an experiment. Change your combobox's AfterUpdate code to this:

Private Sub Combo65_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset
Me.Combo65.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClassID] = """ & Me.Combo65 & """"
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Me.Combo65 = Null
Else
MsgBox "Record not found!"
End If
End Sub

Try your form again. Do you get the message popup about record not found?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



I'm using MS Access 2003 and sorry to say it is not working.
I'm not getting "errors" when I select Combo65.
The dropdown list does go away if you select the new record and
responds
like nothing was selected.
The form does not move to the record respresented by that new item
either.
The AfterUpdate event of Combo65 is set to Null at the end of the
command,
because once the record is selected, the form moves to the record
selected
and the combo box is cleared. I did temporarily removed that code and
it
didn't matter if the Null was there or not.
I did enter a the code as suggested..

Private Sub Form_Close()
Forms!Courses.Requery

Thank you,

:

If the newly added item is showing in the dropdown list of Combo65,
then
you
are requerying that combo box already.

When you say the form will not let you select that new item in the
dropdown
list, what happens when you click on it? Does the dropdown list go
away
but
you don't see the new item in the "textbox" part of the combo box? Do
you
get some type of error? Or are you saying that your form does not
"move"
to
the record represented by that new item.

Your current code for the AfterUpdate event of Combo65 sets the value
of
the
combobox back to Null after the code moves the form to the record
represented by the newly added item. If the form's recordset does not
include a record for that newly added item, the form will stay where
it
is
and the combobox will have nothing in it.

Assuming that your form should have a record that matches the newly
added
item, then you need to requery the form when the "new item" form
closes.
That is best done in the Close event of the "new item" form:

Private Sub Form_Close()
Forms!Courses.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




How would you write the command to requery/refresh data to another
form?

The Combo65 does reflect the update though. The problem is I could
not
select the new record in Combo65 box until I close the form and
reopen
it.

I hope that makes sense.

Thank you!

:

Do you have code that requeries the Combo65 control when the
CreateNewCourseFm closes? You need to do that.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Hello:

I have a combobox set in place to search for records in my
current
form
called Courses and it works great.

Private Sub Combo65_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset
Me.Combo65.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClassID] = """ & Me.Combo65 & """"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.Combo65 = Null
End Sub

When I want to create a new class, I have a button which will
take
me
to
another form to add the information.

Private Sub CreateNewClass_Click()

DoCmd.OpenForm "CreateNewCourseFm", windowmode:=acDialog

End Sub

When I close the CreateNewClassFm it will take be back to the
Courses
form.
The new class will show up in the combo box. The problem is that
the
class
will show up in the combo box, but it won't let me select it
unless
I
close
and reopen the form.

Thank you for your help!

 

[Ken Snell [MVP]]

It's difficult to help troubleshoot what is happening when you do not
provide feedback about a test that I requested that you do. The test would
tell us if the procedure's code is even being called or not, and if it is,
what it's doing. Please do the test and post back with the results. If
nothing happens after you select an item in the combo box, that also is
important to know.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I wish I tell you that it either moved to the record or received a msg.
The
only thing I could tell you is when I close the form and reopen it, the
desired record would show up and move to that record....

Before we move on, tell me what the result of the new code was when it
ran.
You should have either had the form move to the desired record or you
should
have gotten the message box saying that the record was not found. Which
occurred?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

No luck ... :-(

Question: When I press the command button to open a form, is there a
way
to
write a procedure to close the current form as I update the table using
the
other form and then reverse the process? Here is what I have so far on
the
main form..

Private Sub Combo65_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset
Me.Combo65.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClassID] = """ & Me.Combo65 & """"
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Me.Combo65 = Null
Else
MsgBox "Record not found!"
End If
End Sub

Private Sub Form_Close()
Forms!Courses.Requery
End Sub

Private Sub Form_Current()
Me.Combo65.Requery
End Sub

Private Sub CreateNewClass_Click()

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "CreateNewCourseFm"

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd
Me.Combo65.Requery
End Sub

Thank you!


:

Let's try an experiment. Change your combobox's AfterUpdate code to
this:

Private Sub Combo65_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset
Me.Combo65.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClassID] = """ & Me.Combo65 & """"
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Me.Combo65 = Null
Else
MsgBox "Record not found!"
End If
End Sub

Try your form again. Do you get the message popup about record not
found?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



I'm using MS Access 2003 and sorry to say it is not working.
I'm not getting "errors" when I select Combo65.
The dropdown list does go away if you select the new record and
responds
like nothing was selected.
The form does not move to the record respresented by that new item
either.
The AfterUpdate event of Combo65 is set to Null at the end of the
command,
because once the record is selected, the form moves to the record
selected
and the combo box is cleared. I did temporarily removed that code
and
it
didn't matter if the Null was there or not.
I did enter a the code as suggested..

Private Sub Form_Close()
Forms!Courses.Requery

Thank you,

:

If the newly added item is showing in the dropdown list of Combo65,
then
you
are requerying that combo box already.

When you say the form will not let you select that new item in the
dropdown
list, what happens when you click on it? Does the dropdown list go
away
but
you don't see the new item in the "textbox" part of the combo box?
Do
you
get some type of error? Or are you saying that your form does not
"move"
to
the record represented by that new item.

Your current code for the AfterUpdate event of Combo65 sets the
value
of
the
combobox back to Null after the code moves the form to the record
represented by the newly added item. If the form's recordset does
not
include a record for that newly added item, the form will stay
where
it
is
and the combobox will have nothing in it.

Assuming that your form should have a record that matches the newly
added
item, then you need to requery the form when the "new item" form
closes.
That is best done in the Close event of the "new item" form:

Private Sub Form_Close()
Forms!Courses.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




How would you write the command to requery/refresh data to
another
form?

The Combo65 does reflect the update though. The problem is I
could
not
select the new record in Combo65 box until I close the form and
reopen
it.

I hope that makes sense.

Thank you!

:

Do you have code that requeries the Combo65 control when the
CreateNewCourseFm closes? You need to do that.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Hello:

I have a combobox set in place to search for records in my
current
form
called Courses and it works great.

Private Sub Combo65_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset
Me.Combo65.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClassID] = """ & Me.Combo65 & """"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.Combo65 = Null
End Sub

When I want to create a new class, I have a button which will
take
me
to
another form to add the information.

Private Sub CreateNewClass_Click()

DoCmd.OpenForm "CreateNewCourseFm", windowmode:=acDialog

End Sub

When I close the CreateNewClassFm it will take be back to the
Courses
form.
The new class will show up in the combo box. The problem is
that
the
class
will show up in the combo box, but it won't let me select it
unless
I
close
and reopen the form.

Thank you for your help!

 

[NFL]

I apologize for not explaining...

The code for the dropdown menu did not identify the new record and the msg
"Record Not Found!" did not show up either on the 1st attempt. On the 2d
attempt the record was identified and after I select that record, it did not
move to that record.

It's difficult to help troubleshoot what is happening when you do not
provide feedback about a test that I requested that you do. The test would
tell us if the procedure's code is even being called or not, and if it is,
what it's doing. Please do the test and post back with the results. If
nothing happens after you select an item in the combo box, that also is
important to know.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I wish I tell you that it either moved to the record or received a msg.
The
only thing I could tell you is when I close the form and reopen it, the
desired record would show up and move to that record....

Before we move on, tell me what the result of the new code was when it
ran.
You should have either had the form move to the desired record or you
should
have gotten the message box saying that the record was not found. Which
occurred?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


No luck ... :-(

Question: When I press the command button to open a form, is there a
way
to
write a procedure to close the current form as I update the table using
the
other form and then reverse the process? Here is what I have so far on
the
main form..

Private Sub Combo65_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset
Me.Combo65.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClassID] = """ & Me.Combo65 & """"
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Me.Combo65 = Null
Else
MsgBox "Record not found!"
End If
End Sub

Private Sub Form_Close()
Forms!Courses.Requery
End Sub

Private Sub Form_Current()
Me.Combo65.Requery
End Sub

Private Sub CreateNewClass_Click()

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "CreateNewCourseFm"

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd
Me.Combo65.Requery
End Sub

Thank you!


:

Let's try an experiment. Change your combobox's AfterUpdate code to
this:

Private Sub Combo65_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset
Me.Combo65.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClassID] = """ & Me.Combo65 & """"
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Me.Combo65 = Null
Else
MsgBox "Record not found!"
End If
End Sub

Try your form again. Do you get the message popup about record not
found?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



I'm using MS Access 2003 and sorry to say it is not working.
I'm not getting "errors" when I select Combo65.
The dropdown list does go away if you select the new record and
responds
like nothing was selected.
The form does not move to the record respresented by that new item
either.
The AfterUpdate event of Combo65 is set to Null at the end of the
command,
because once the record is selected, the form moves to the record
selected
and the combo box is cleared. I did temporarily removed that code
and
it
didn't matter if the Null was there or not.
I did enter a the code as suggested..

Private Sub Form_Close()
Forms!Courses.Requery

Thank you,

:

If the newly added item is showing in the dropdown list of Combo65,
then
you
are requerying that combo box already.

When you say the form will not let you select that new item in the
dropdown
list, what happens when you click on it? Does the dropdown list go
away
but
you don't see the new item in the "textbox" part of the combo box?
Do
you
get some type of error? Or are you saying that your form does not
"move"
to
the record represented by that new item.

Your current code for the AfterUpdate event of Combo65 sets the
value
of
the
combobox back to Null after the code moves the form to the record
represented by the newly added item. If the form's recordset does
not
include a record for that newly added item, the form will stay
where
it
is
and the combobox will have nothing in it.

Assuming that your form should have a record that matches the newly
added
item, then you need to requery the form when the "new item" form
closes.
That is best done in the Close event of the "new item" form:

Private Sub Form_Close()
Forms!Courses.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




How would you write the command to requery/refresh data to
another
form?

The Combo65 does reflect the update though. The problem is I
could
not
select the new record in Combo65 box until I close the form and
reopen
it.

I hope that makes sense.

Thank you!

:

Do you have code that requeries the Combo65 control when the
CreateNewCourseFm closes? You need to do that.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Hello:

I have a combobox set in place to search for records in my
current
form
called Courses and it works great.

Private Sub Combo65_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset
Me.Combo65.Requery
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClassID] = """ & Me.Combo65 & """"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.Combo65 = Null
End Sub

When I want to create a new class, I have a button which will
take
me
to
another form to add the information.

Private Sub CreateNewClass_Click()

DoCmd.OpenForm "CreateNewCourseFm", windowmode:=acDialog

End Sub

When I close the CreateNewClassFm it will take be back to the
Courses
form.
The new class will show up in the combo box. The problem is
that
the
class
will show up in the combo box, but it won't let me select it
unless
I
close
and reopen the form.

Thank you for your help!

 

[Ken Snell [MVP]]

It appears that two issues are at work here.

First, you will need to requery your form's data after you add a new Course.
I assume that your form is using a query based partially on the Courses
table. This typically would be done by your "CreateNewCourseFm" form's Close
event:

Private Sub Form_Close
Forms!Courses.Requery
End Sub

The above code should add the new course to the form so that your form can
display it.

The fact that you're not getting any result from your AfterUpdate event
procedure code for the combo box suggests to me that the code is not
actually associated with the On After Update property for that combo box.
Open the form in design view, click on the combo box, open the Properties
window, and click on the Event tab. If the box next to On After Update is
empty, click in the box, select "[Event Procedure]" from the dropdown list.
Then click on the three-dot box to the right of the box. The Visual Basic
Editor should open and show you your AfterUpdate code. Save and close the
form.

Now try your form.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[NFL]

That did the trick! Thank you so much for your time and patience!!!

It appears that two issues are at work here.

First, you will need to requery your form's data after you add a new Course.
I assume that your form is using a query based partially on the Courses
table. This typically would be done by your "CreateNewCourseFm" form's Close
event:

Private Sub Form_Close
Forms!Courses.Requery
End Sub

The above code should add the new course to the form so that your form can
display it.

The fact that you're not getting any result from your AfterUpdate event
procedure code for the combo box suggests to me that the code is not
actually associated with the On After Update property for that combo box.
Open the form in design view, click on the combo box, open the Properties
window, and click on the Event tab. If the box next to On After Update is
empty, click in the box, select "[Event Procedure]" from the dropdown list.
Then click on the three-dot box to the right of the box. The Visual Basic
Editor should open and show you your AfterUpdate code. Save and close the
form.

Now try your form.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I apologize for not explaining...

The code for the dropdown menu did not identify the new record and the msg
"Record Not Found!" did not show up either on the 1st attempt. On the 2d
attempt the record was identified and after I select that record, it did
not
move to that record.