Combo Box

[sike11 via AccessMonster.com]

Hi people,

Please help.

I have a form which asks the user to put in a search criteria and is linked
to a combo box which returns a list of names for the user to action.. The
form works great. My problem is that when I clear the text boxes and the
combo box for the next search, I get the value of the old results instead of
returning another list of names which fulfils the next search criteria. Can
anybody help?
I have put after the update to requerry the combo box but I still get the
same problem. Please help as I am beginning to tear my hair out!!

Regards,

Mary.

 

[Guest]

What method are you using to populate your combobox? Value List or Query? If
you're using a value list, you should clear the old values before you add new
ones. You can do this by setting the rowsource property to "". If it's a sql
string, could you post your string and the code you're using?

Barry

 

[Guest]

What method are you using to populate your combobox? Value List or Query? If
you're using a value list, you should clear the old values before you add new
ones. You can do this by setting the rowsource property to "". If it's a sql
string, could you post your string and the code you're using?

Barry

 

[sike11 via AccessMonster.com]

Hi,

I am using a query. This is what is in the rowsource property:

"SELECT [qryQOE-ManualUpdate].student_studentreference, [qryQOE-ManualUpdate].
[student-surname], [qryQOE-ManualUpdate].student_forenames, Trim(
[student_forenames]) & " " & Trim([student-surname]) AS Student FROM [qryQOE-
ManualUpdate] ORDER BY [qryQOE-ManualUpdate].[student-surname];"

Thank you in advance.

What method are you using to populate your combobox? Value List or Query? If
you're using a value list, you should clear the old values before you add new
ones. You can do this by setting the rowsource property to "". If it's a sql
string, could you post your string and the code you're using?

Barry

Hi people,

[quoted text clipped - 12 lines]

Mary.

 

[Guest]

You mentioned a text box. Does qryQOE-ManualUpdate use a textbox as a
criterion? Please also post the code in the procedure where you requery the
combobox.

Barry

Hi,

I am using a query. This is what is in the rowsource property:

"SELECT [qryQOE-ManualUpdate].student_studentreference, [qryQOE-ManualUpdate].
[student-surname], [qryQOE-ManualUpdate].student_forenames, Trim(
[student_forenames]) & " " & Trim([student-surname]) AS Student FROM [qryQOE-
ManualUpdate] ORDER BY [qryQOE-ManualUpdate].[student-surname];"

Thank you in advance.

What method are you using to populate your combobox? Value List or Query? If
you're using a value list, you should clear the old values before you add new
ones. You can do this by setting the rowsource property to "". If it's a sql
string, could you post your string and the code you're using?

Barry

Hi people,

[quoted text clipped - 12 lines]

Mary.

 

[sike11 via AccessMonster.com]

Hi Barry,

Here is the code in the AfterUpdate event of the combobox

Private Sub Student_AfterUpdate()
Me.Student.Requery
Me.txtSurname = Me.Student.Column(1)
Me.txtForenames = Me.Student.Column(2)

End Sub

This is the code for the 2 textboxes which feeds the combobox:

"SELECT Trim(Str([student_id])) AS S_ID, Vw_QOE_ManualEntry.
student_studentreference,
Vw_QOE_ManualEntry.[student-surname], Vw_QOE_ManualEntry.
student_forenames
FROM Vw_QOE_ManualEntry
WHERE (((Vw_QOE_ManualEntry.[student-surname]) Like nz([Forms]![frmQOE-
Manual-Update]![txtSurname]))
AND ((Vw_QOE_ManualEntry.student_forenames) Like nz([Forms]![frmQOE-Manual-
Update]![txtForenames])));

This is the code in the Update command button:

Private Sub cmdUpdateQOE_Table_Click()
On Error GoTo Err_cmdUpdateQOE_Table_Click

Dim stDocName As String
Dim c As Control
Dim s, s1, s2, s3 As String

Dim i As Integer

DoCmd.SetWarnings False 'Prevent warning messages

If CheckName(txtSurname, txtForenames) Then
Call InsertNew
End If

MsgBox "Records Added!", vbExclamation

For Each c In Me.Controls
If c.ControlType = acComboBox Or c.ControlType = acTextBox
Then
c.Value = Null
End If
Next c

Me.Requery

DoCmd.SetWarnings True 'Reset warnings

Exit_cmdUpdateQOE_Table_Click:
Exit Sub

Err_cmdUpdateQOE_Table_Click:
MsgBox Err.Description
Resume Exit_cmdUpdateQOE_Table_Click
End Sub

I am not sure what I am doing wrong. The form should present a fresh screen
for new search after I press the Update button.

Thank you in advance.

Mary.

You mentioned a text box. Does qryQOE-ManualUpdate use a textbox as a
criterion? Please also post the code in the procedure where you requery the
combobox.

Barry

Hi,

[quoted text clipped - 19 lines]

 

[Guest]

Try moving Me.Student.Requery to the end of the Update routine. It's not
doing anything useful where it is now.

Barry

Hi Barry,

Here is the code in the AfterUpdate event of the combobox

Private Sub Student_AfterUpdate()
Me.Student.Requery
Me.txtSurname = Me.Student.Column(1)
Me.txtForenames = Me.Student.Column(2)

End Sub

This is the code for the 2 textboxes which feeds the combobox:

"SELECT Trim(Str([student_id])) AS S_ID, Vw_QOE_ManualEntry.
student_studentreference,
Vw_QOE_ManualEntry.[student-surname], Vw_QOE_ManualEntry.
student_forenames
FROM Vw_QOE_ManualEntry
WHERE (((Vw_QOE_ManualEntry.[student-surname]) Like nz([Forms]![frmQOE-
Manual-Update]![txtSurname]))
AND ((Vw_QOE_ManualEntry.student_forenames) Like nz([Forms]![frmQOE-Manual-
Update]![txtForenames])));

This is the code in the Update command button:

Private Sub cmdUpdateQOE_Table_Click()
On Error GoTo Err_cmdUpdateQOE_Table_Click

Dim stDocName As String
Dim c As Control
Dim s, s1, s2, s3 As String

Dim i As Integer

DoCmd.SetWarnings False 'Prevent warning messages

If CheckName(txtSurname, txtForenames) Then
Call InsertNew
End If

MsgBox "Records Added!", vbExclamation

For Each c In Me.Controls
If c.ControlType = acComboBox Or c.ControlType = acTextBox
Then
c.Value = Null
End If
Next c

Me.Requery

DoCmd.SetWarnings True 'Reset warnings

Exit_cmdUpdateQOE_Table_Click:
Exit Sub

Err_cmdUpdateQOE_Table_Click:
MsgBox Err.Description
Resume Exit_cmdUpdateQOE_Table_Click
End Sub

I am not sure what I am doing wrong. The form should present a fresh screen
for new search after I press the Update button.

Thank you in advance.

Mary.

You mentioned a text box. Does qryQOE-ManualUpdate use a textbox as a
criterion? Please also post the code in the procedure where you requery the
combobox.

Barry

Hi,

[quoted text clipped - 19 lines]

Mary.

 

[sike11 via AccessMonster.com]

Hi Barry,

Thank for the suggestion. No luck. It still held the value of the previous
result. Any other suggestions?

Regards,

Mary.

Try moving Me.Student.Requery to the end of the Update routine. It's not
doing anything useful where it is now.

Barry

Hi Barry,

[quoted text clipped - 75 lines]

 

[Guest]

A few questions:
What does the InsertNew routine do? Does it insert a new record or update an
existing record? Are you sure this insert/update is happening?
What is Vw_QOE_ManualEntry? Does this get values from the same table you're
inserting into?
Is the SQL string the rowsource of the combobox?

Barry

Hi Barry,

Thank for the suggestion. No luck. It still held the value of the previous
result. Any other suggestions?

Regards,

Mary.

Try moving Me.Student.Requery to the end of the Update routine. It's not
doing anything useful where it is now.

Barry

Hi Barry,

[quoted text clipped - 75 lines]

Mary.

 

[sike11 via AccessMonster.com]

Hey Barry,

The InsertNew routine DOES insert a record into a table which is definitely
happening. I check the table after I run the form and it is holding the
information inserted via the form.
VwQOE_QOE_ManualEntry is the table on which qryQOE-ManualUpdate is based. I
do get values from this table but I then insert into a separate table
(tblQOE_New).
The rowsource of the combobox is based on qryQOE-ManualUpdate from which I
then choose the field(s) I want to display.
No, the SQL string in the rowsource is as follows:

" SELECT [qryQOE-ManualUpdate].student_studentreference, [qryQOE-
ManualUpdate].[student-surname],
[qryQOE-ManualUpdate].student_forenames, Trim([student_forenames]) & " " &
Trim([student-surname])
AS Student FROM [qryQOE-ManualUpdate] ORDER BY [qryQOE-ManualUpdate].
[student-surname];"

Have I totally confused you or does this make sense?

Please advise.

Regards,

Mary.

A few questions:
What does the InsertNew routine do? Does it insert a new record or update an
existing record? Are you sure this insert/update is happening?
What is Vw_QOE_ManualEntry? Does this get values from the same table you're
inserting into?
Is the SQL string the rowsource of the combobox?

Barry

Hi Barry,

[quoted text clipped - 15 lines]

 

[Guest]

Well maybe I am confused. You're inserting records into a table called
tblQOE_New but the rowsource of your combobox doesn't look at this table. Are
the values in both tables or just the "new" table?

Barry

Hey Barry,

The InsertNew routine DOES insert a record into a table which is definitely
happening. I check the table after I run the form and it is holding the
information inserted via the form.
VwQOE_QOE_ManualEntry is the table on which qryQOE-ManualUpdate is based. I
do get values from this table but I then insert into a separate table
(tblQOE_New).
The rowsource of the combobox is based on qryQOE-ManualUpdate from which I
then choose the field(s) I want to display.
No, the SQL string in the rowsource is as follows:

" SELECT [qryQOE-ManualUpdate].student_studentreference, [qryQOE-
ManualUpdate].[student-surname],
[qryQOE-ManualUpdate].student_forenames, Trim([student_forenames]) & " " &
Trim([student-surname])
AS Student FROM [qryQOE-ManualUpdate] ORDER BY [qryQOE-ManualUpdate].
[student-surname];"

Have I totally confused you or does this make sense?

Please advise.

Regards,

Mary.

A few questions:
What does the InsertNew routine do? Does it insert a new record or update an
existing record? Are you sure this insert/update is happening?
What is Vw_QOE_ManualEntry? Does this get values from the same table you're
inserting into?
Is the SQL string the rowsource of the combobox?

Barry

Hi Barry,

[quoted text clipped - 15 lines]

Mary.

 

[sike11 via AccessMonster.com]

Hi Barry,

The values are in both tables. The Vw_QOE_ManualEntry table is linked to a
SQL server. We just link to gain information about students. The rowsource of
the combo box is based on the query which uses the Vw_QOE_ManualEntry,
therefore, it does look at the table.
Please help.

Mary.

Well maybe I am confused. You're inserting records into a table called
tblQOE_New but the rowsource of your combobox doesn't look at this table. Are
the values in both tables or just the "new" table?

Barry

Hey Barry,

[quoted text clipped - 37 lines]

 

[Guest]

I guess I'd want to know if the problem is with the data or with the form.
Can you paste the rowsource into a query, run the update, and immediately run
the query to see if it returns an updated list? If it does, then the problem
is with the requery of the combobox.

Barry

Hi Barry,

The values are in both tables. The Vw_QOE_ManualEntry table is linked to a
SQL server. We just link to gain information about students. The rowsource of
the combo box is based on the query which uses the Vw_QOE_ManualEntry,
therefore, it does look at the table.
Please help.

Mary.

Well maybe I am confused. You're inserting records into a table called
tblQOE_New but the rowsource of your combobox doesn't look at this table. Are
the values in both tables or just the "new" table?

Barry

Hey Barry,

[quoted text clipped - 37 lines]

Mary.

 

[sike11 via AccessMonster.com]

Hi Barry,

Sorry to have taken so long to reply.

I tried what you suggested. Everything works fine apart from the requerry of
the combobox. The query returns the required list. I am unsure how to proceed
from here.
Any ideas?

I guess I'd want to know if the problem is with the data or with the form.
Can you paste the rowsource into a query, run the update, and immediately run
the query to see if it returns an updated list? If it does, then the problem
is with the requery of the combobox.

Barry

Hi Barry,

[quoted text clipped - 17 lines]

 

[Guest]

If the table is being correctly updated, the requery method should work.
There must be something else going on. Without seeing it in person, I'm out
of ideas.

Barry

Hi Barry,

Sorry to have taken so long to reply.

I tried what you suggested. Everything works fine apart from the requerry of
the combobox. The query returns the required list. I am unsure how to proceed
from here.
Any ideas?

I guess I'd want to know if the problem is with the data or with the form.
Can you paste the rowsource into a query, run the update, and immediately run
the query to see if it returns an updated list? If it does, then the problem
is with the requery of the combobox.

Barry

Hi Barry,

[quoted text clipped - 17 lines]

Mary.

 

[sike11 via AccessMonster.com]

Hello Barry,

Success at last!!!! I managed to get it working by requerying the combo box
in the AfterUpdate event of 1 of the textboxes.

Thank you very much for all the help you gave me, very much appreciated!!

Regards,

Mary.

If the table is being correctly updated, the requery method should work.
There must be something else going on. Without seeing it in person, I'm out
of ideas.

Barry

Hi Barry,

[quoted text clipped - 17 lines]