Using a combo box to search for a record - error message

[Bob Matthews]

Hi all

I am using Access 2007.

My Table named Debtors has the following field

Surname as a Text field

My RowSource is :-

SELECT DISTINCTROW [Debtors].[Surname] FROM Debtors ORDER BY [Surname];

My After Update Event Procedure is :-

Private Sub Combo76_AfterUpdate()

DoCmd.ShowAllRecords
Me!txtSurname.SetFocus
DoCmd.FindRecord Me!Combo76

Me!Combo76.Value = ""

End Sub

and I get the following error message.................

Run-time error '2465'
Access can't find the field 'txtSurname'

Where have I gone wrong?

Bob

 

[John W. Vinson]

Hi all

I am using Access 2007.

My Table named Debtors has the following field

Surname as a Text field

My RowSource is :-

SELECT DISTINCTROW [Debtors].[Surname] FROM Debtors ORDER BY [Surname];

My After Update Event Procedure is :-

Private Sub Combo76_AfterUpdate()

DoCmd.ShowAllRecords
Me!txtSurname.SetFocus
DoCmd.FindRecord Me!Combo76

Me!Combo76.Value = ""

End Sub

and I get the following error message.................

Run-time error '2465'
Access can't find the field 'txtSurname'

Where have I gone wrong?

I would guess that the name of the field in the table is Surname, not
txtSurname.

Why not just use the builtin combo box toolbox wizard, to create a combo box
"Use this combo to find records"? If you want to find all records for a
surname, e.g. Bill Jones' debts, David Jones, Mary Jones, Zachary Jones, you
could change the AfterUpdate event to

Private Sub Combo76_AfterUpdate() ' you *could* give the combo a real name
Me.Filter = "[Surname] = """ & Me!Combo76 & """"
Me.FilterOn = True
End Sub

 

[Bob Matthews]

Thank you John

I cut and pasted your suggested After Update Event vode in...............

The combo box will not accept any input
If you drop it down - you see the firs 16 entries from the table
But selecting any record does not update the form ????

Bob

Hi all

I am using Access 2007.

My Table named Debtors has the following field

Surname as a Text field

My RowSource is :-

SELECT DISTINCTROW [Debtors].[Surname] FROM Debtors ORDER BY [Surname];

My After Update Event Procedure is :-

Private Sub Combo76_AfterUpdate()

DoCmd.ShowAllRecords
Me!txtSurname.SetFocus
DoCmd.FindRecord Me!Combo76

Me!Combo76.Value = ""

End Sub

and I get the following error message.................

Run-time error '2465'
Access can't find the field 'txtSurname'

Where have I gone wrong?

I would guess that the name of the field in the table is Surname, not
txtSurname.

Why not just use the builtin combo box toolbox wizard, to create a combo
box
"Use this combo to find records"? If you want to find all records for a
surname, e.g. Bill Jones' debts, David Jones, Mary Jones, Zachary Jones,
you
could change the AfterUpdate event to

Private Sub Combo76_AfterUpdate() ' you *could* give the combo a real
name
Me.Filter = "[Surname] = """ & Me!Combo76 & """"
Me.FilterOn = True
End Sub

 

[John W. Vinson]

Thank you John

I cut and pasted your suggested After Update Event vode in...............

The combo box will not accept any input
If you drop it down - you see the firs 16 entries from the table
But selecting any record does not update the form ????

The combo box should be unbound - nothing in its Control Source.

Could you post the RowSource (the SQL) and your actual code?

 

[Bob Matthews]

Hi John

The combo box should be unbound - nothing in its Control Source.

correct - it is unbound

Could you post the RowSource (the SQL) and your actual code?

The RowSource is :-

SELECT DISTINCTROW [Debtors].[Surname] FROM Debtors ORDER BY [Surname];

Not sure what you mean by 'actual code'

Bob

 

[John W. Vinson]

Hi John

The combo box should be unbound - nothing in its Control Source.

correct - it is unbound

Could you post the RowSource (the SQL) and your actual code?

The RowSource is :-

SELECT DISTINCTROW [Debtors].[Surname] FROM Debtors ORDER BY [Surname];

Not sure what you mean by 'actual code'

Bob

There should be either a macro or (if you correctly followed my suggestion)
some VBA code in the AfterUpdate event of the combo box. If the AfterUpdate
event shows [Event Procedure] please click the ... icon by it to open the VBA
editor, and copy and paste the code here. If it contains something else,
please post that. If it's blank... then your combo won't do anything at all!

 

[Bob Matthews]

Hi John

The code you are asking about is as follows.......................

Private Sub Combo76_AfterUpdate() ' you *could* give the combo a real name
Me.Filter = "[Surname] = """ & Me!Combo76 & """"
Me.FilterOn = True
End Sub

Bob

 

[John W. Vinson]

Thank you John

I cut and pasted your suggested After Update Event vode in...............

The combo box will not accept any input
If you drop it down - you see the firs 16 entries from the table
But selecting any record does not update the form ????

Sounds like the combo's Enabled property is off, or its Locked property is on
(they should be Yes and No respectively), or the Form's AllowUpdates property
is No (it needs to be Yes for the combo box to allow a selection).