navigation in single form view

[Guest]

I am trying to use a combo box as navigation for a form in single form view,
allowing me (hopefully) to select the record for the form to display from a
pull-down list. I am trying to use the following code to achieve this:

Private Sub Combo40_AfterUpdate()

Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "SupplierTbl.Company_Name=" & Me.Combo40.Column(0)
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
Else
MsgBox "Record not found."
End If

Set rst = Nothing
End Sub

I have had some difficulty with this however. I'm not sure whether it has to
do with the SQL statement for the row source in the combo (which currently
reads: SELECT SupplierListTbl.SupplierID,
SupplierListTbl.[Company Name] FROM SupplierListTbl ORDER BY [Company
Name] or whether I'm actually barking up the wrong tree entirely. Can
anyone either point me in the right direction for making this thing work or
suggest another approach (basically I want to jump to a record instantly
without having to scroll through all contiguous records or needing to know
the record number)

thanks

Jake

 

[Dan Sears]

How about something like this?

Private Sub Combo40_AfterUpdate()

If not IsNull(Me!Combo40) then
Me!Company_Name.SetFocus
DoCmd.FindRecord Me!Combo40
End If

End Sub

Something to keep in mind:
The record source for Combo40 should be Company_Name in your Supplier
table.
If the same Company_Name exists in multiple records, you'll be sent to
the first instance.

 

[Guest]

Dan,

thanks a lot for your help, unfortunately it still doesn't work - Iclick on
an item from the combo box and literally *nothing* happens; the list doesn't
close, the record doesn't change, nothing.

When you say "The record source for Combo40 should be Company_Name", would
that go in Row Source or Control Source in the properties>data tab? If it's
the control source do I need an SQL statement to describe it or do I just
name it?

I think I might be a bit out of my depth here, but I need to get this done.
Please be as patronising as necessary when explaining this!

Many thanks

Jake

How about something like this?

Private Sub Combo40_AfterUpdate()

If not IsNull(Me!Combo40) then
Me!Company_Name.SetFocus
DoCmd.FindRecord Me!Combo40
End If

End Sub

Something to keep in mind:
The record source for Combo40 should be Company_Name in your Supplier
table.
If the same Company_Name exists in multiple records, you'll be sent to
the first instance.

I am trying to use a combo box as navigation for a form in single form view,
allowing me (hopefully) to select the record for the form to display from a
pull-down list. I am trying to use the following code to achieve this:

Private Sub Combo40_AfterUpdate()

Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "SupplierTbl.Company_Name=" & Me.Combo40.Column(0)
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
Else
MsgBox "Record not found."
End If

Set rst = Nothing
End Sub

I have had some difficulty with this however. I'm not sure whether it has to
do with the SQL statement for the row source in the combo (which currently
reads: SELECT SupplierListTbl.SupplierID,
SupplierListTbl.[Company Name] FROM SupplierListTbl ORDER BY [Company
Name] or whether I'm actually barking up the wrong tree entirely. Can
anyone either point me in the right direction for making this thing work or
suggest another approach (basically I want to jump to a record instantly
without having to scroll through all contiguous records or needing to know
the record number)

thanks

Jake

 

[Marshall Barton]

I am trying to use a combo box as navigation for a form in single form view,
allowing me (hopefully) to select the record for the form to display from a
pull-down list. I am trying to use the following code to achieve this:

Private Sub Combo40_AfterUpdate()

Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "SupplierTbl.Company_Name=" & Me.Combo40.Column(0)
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
Else
MsgBox "Record not found."
End If

Set rst = Nothing
End Sub

I have had some difficulty with this however. I'm not sure whether it has to
do with the SQL statement for the row source in the combo (which currently
reads: SELECT SupplierListTbl.SupplierID,
SupplierListTbl.[Company Name] FROM SupplierListTbl ORDER BY [Company
Name] or whether I'm actually barking up the wrong tree entirely. Can
anyone either point me in the right direction for making this thing work or
suggest another approach (basically I want to jump to a record instantly
without having to scroll through all contiguous records or needing to know
the record number)

The combo box must be unbound and should be in either the
form header or footer section.

The real problem is that you are using FindFirst to search
the [Company Name] field, but the value from the combo box
is the SupplierID. Also note that you should not use the
table name in the FindFirst where clause:

rst.FindFirst "SupplierID=" & Me.Combo40.Column(0)

If the combo box's BoundColumn is 1, then you don't need to
use the Column(0) part of that:

rst.FindFirst "SupplierID=" & Me.Combo40

 

[Guest]

Thanks Marsh,

haven't had time to tinker with this properly yet, but it looks like you've
out me on the right track

Jake

I am trying to use a combo box as navigation for a form in single form view,
allowing me (hopefully) to select the record for the form to display from a
pull-down list. I am trying to use the following code to achieve this:

Private Sub Combo40_AfterUpdate()

Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "SupplierTbl.Company_Name=" & Me.Combo40.Column(0)
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
Else
MsgBox "Record not found."
End If

Set rst = Nothing
End Sub

I have had some difficulty with this however. I'm not sure whether it has to
do with the SQL statement for the row source in the combo (which currently
reads: SELECT SupplierListTbl.SupplierID,
SupplierListTbl.[Company Name] FROM SupplierListTbl ORDER BY [Company
Name] or whether I'm actually barking up the wrong tree entirely. Can
anyone either point me in the right direction for making this thing work or
suggest another approach (basically I want to jump to a record instantly
without having to scroll through all contiguous records or needing to know
the record number)

The combo box must be unbound and should be in either the
form header or footer section.

The real problem is that you are using FindFirst to search
the [Company Name] field, but the value from the combo box
is the SupplierID. Also note that you should not use the
table name in the FindFirst where clause:

rst.FindFirst "SupplierID=" & Me.Combo40.Column(0)

If the combo box's BoundColumn is 1, then you don't need to
use the Column(0) part of that:

rst.FindFirst "SupplierID=" & Me.Combo40