Synchronising Form to the value in the combobox 1

[thembela]

I have a form with a combo box on it. I’m trying to synchronise the form to the value in the combo box. Here’s my code attached to the Apply Filter Action of the Macro.

[Broker Number] = [Forms]![frmBroker]![CboFind]

[Broker number] is the name of the field in the underlying table and [CboFind] is the name of the field on the form and it contains the value I want to match. What could be wrong.

 

[KenReay]

Hi

First a few sources of information

See the code generated by the combo box wizard

or

See FAQ in this forum, Combo Boxes, Combo Box as a navigation toll

You do not say which version of Access, or if you are using ADO or DAO, but basically you need code like so

Dim rs as Recordset
Set Rs = ME.Recordsetclone
rs.findfirst "[Broker Number] = " & [Forms]![frmBroker]![CboFind]
If rs.nomatch then
' code here for error message or whatever
else
me.bookmark = rs.bookmark
end if
set rs = nothing

Not if [Broker Number] is a string you need

rs.findfirst "[Broker Number] = '" & [Forms]![frmBroker]![CboFind] & "' "



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[thembela]

Thanks KenReay, you are an absolute satr .it worked.

 

[thembela]

KenReay, there's something baffling me here.Why is it that the ApplyFilterAction on the macro couldn't work.I attached the macro to the After_update event of the ComboBox.

Here's my macro to find a Record using the ApplyFilter Section:

Action Action Arguments
ApplFilter Where Condition:
[Broker Number]=[Forms]![frmBroker]![CboFind]

I'm using Access 2003

 

[KenReay]

Hi

I do not do macros, they are yesterdays technology, stick with VBA

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[thembela]

KenReay, everything seems to work perectly.Now the problem is when i enter the value that is not in the list .I want an error message to display that the value entered isn't in the list.And on top of that whatever value that was entered( not in the list must be deleted( reset to null) here's a piece of my code:

Private Sub Benton_AfterUpdate()
Dim rs As Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[BRK_ENTITY_NO] = " & [Forms]![frmBroker]![Benton]
If (DCount("*", "[Broker Information]", "[BRK_ENTITY_NO] = [forms]![frmBroker]![Benton]") = 0) Then
MsgBox " OOPS!!!THE VALUE ENTERED," & [Benton] & " IS NOT A VALID BROKER NUMBER"

Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing

End Sub

 

[KenReay]

hi

why didn't you use:

If rs.nomatch then

as I suggested, you are doing two lookups, not very efficient

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK