Navigation for single form view

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that works best in single form view (it's too big and unwieldy
for continuous view) displaying several records (all from the same table)
about various suppliers that my company uses. What i would like to do is set
up some form of navigation, either in the form of a drop-down list of company
names, allowing the user to jump to a given record without having to know the
record number, or some form of sidebar, like a navigation frame in an html
website. I've tried doing this with a combo box which looks up fields from a
list of suppliers from a separate table, but I can't figure out how to make
it control which record the form looks at. Am I barking completely up the
wrong tree here? Any thoughts on how i might best achieve this would most
gratefully received.

Cheers,

Jake
 
The simple way to do it with a combo is to use the RecordsetClone of your
form. Note you'll have to have a reference to Microsoft DAO Object Library
to do this:

Sub YourCombo_AfterUpdate()

Dim rst As DAO.REcordset

set rst = Me.RecordsetClone
rst.FindFirst "YourIDField=" & Me.YourCombo.Column(0)
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
Else
Msgbox "Record not found."
End If

set rst = NOthing
End sub

The YourCOmbo.Column(0) indicates the first column, since they're 0-based. I
don't know the .RowSource of your combo, therefore can't tell you which
column to use, however your combo should include the UniqueID (i.e. Primary
Key) of the form's underlying table.
 
Thanks Scott, but i don't quite understand where your code should go - is
that an OnClick event? (i'm assuming my user will want to use the mouse to
select the record)

Cheers,
Jake
 
actually please ignore my last i was being a spacker it's clearly an
AfterUpdate event!!

Cheers for the help
 
OK, tried it but I can't get it to work. At the moment when I pull down the
combo box, clicking on any given supplier from the list has no effect at all,
the list doesn't even close.

Have I deployed your code right, or are there any obvious holes in 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

the lookup field in the combo box comes from a table called
'SupplierListTbl', which contains only the 'Company Name' field and a
corresponding autonumber field 'SupplierID'. Both of these fields are
duplicated in the table 'SupplierTbl' which is the data source for the form.
Should I be doing this all on the same table or something? Also I have
defined a 1-1 relationship between the two 'SupplierID' fields. I have to
confess to not fully understanding the relationships view - is there any
point to having done this?

Sorry to have come back with so many questions but I have scented the kill
now and can't rest until i'Ve solved it!!

Cheers,

jake

**SCHNIP!**
 
What's the .RowSource of Combo40? I'm betting that the FIRST column in
Combo40 (which is the column you're referencing in your code by looking at
..Column(0)) is your autonumber field. If your .RowSource is something like
this;

SELECT ID, Company_Name FROM SupplierTbl

Then change this line:

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

If you want to use the Company_Name field, you would do this

rst.FindFirst "SupplierTbl.Company_Name='" & Me.Combo40.Column(0) & "'"
--------------------------------------------^----------------------------------^

Note the Single Quotes { ' } at the locations indicated above.
 
Hmmm...

Neither of those works, but I am getting an error message (not necessarily
as the result of those changes) which reads "the value you have entered is
not valid for this field. For example you may have entered text into a
numeric field or a number that is larger than the field size permits" This
happens with both the arrangements of code you suggested below, but only when
using the centre wheel of my mouse to scroll through the list items, not when
dragging and clicking to select from the list, which still has no effect. The
computer doesn;t seem to respond to the obscenities I have been screaming at
it either...
 
The Row source reads as follows: SELECT SupplierListTbl.SupplierID,
SupplierListTbl.[Company Name] FROM SupplierListTbl ORDER BY [Company Name];

and the row source type is set to Table/Query. would this make a difference?
 
Back
Top