Form like File Explorer for data?

[Scott2S]

Is there a way in Access to display a scroll window of record names on a left pane and then when the user clicks on one of these records on the left that the data from that record will populate a form on the right - sorta like file explorer does ? Do I need a custom ActiveX control or do I use subforms?

 

[tlbroadbent]

There are at least a couple of ways to approach this with standard Access tools. Here is a simple method that I recommend because it is very simple and requires little coding.

1- Create a form based on the table or a query. All columns in the table would be bound to the form.
2- Add a list box that had as it's row source a query of the key fields in the table. Make sure one of those fields is the record identifier. I use the name RecID.
3- Add the following code in the After Update event of the list box. With this code, everytime the user selects a record in the list box the form will move to that record in the table.

Private Sub RecordsListbox_AfterUpdate()
Me.RecordsetClone.FindFirst "[RecID] = " & Me.RecordsListbox
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[Scott2S]

Cool. Thanks for the quick reply! So I don't have to do this with a subform?

 

[tlbroadbent]

No subform required for this technique. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[Scott2S]

Thanks. I'll give it a shot. You said there are at least two ways to do this - what was the other?

 

[tlbroadbent]

1) Create an unbound form and add rest boxes for all the columns on the table except perhaps the RecID or primary key columns.
2) Create the list box as outlined in the previuos method.
3) Add the following code to the After Update event of the listbox so that when the user chooses an entry in the listbox, the code is executed.

Private Sub RecordsListbox_AfterUpdate()
textbox1 = DLookup("[Col1]","table1","[RecID]=" & Me.RecordsListbox)
textbox2 = DLookup("[Col2]","table1","[RecID]=" & Me.RecordsListbox)
textbox3 = DLookup("[Col3]","table1","[RecID]=" & Me.RecordsListbox)
textbox4 = DLookup("[Col4]","table1","[RecID]=" & Me.RecordsListbox)

Etc.

End Sub

Another option would be to create a recordset from the table and populate the textboxes with fields from the recordset. Numerous examples can be found in HELP and online.

-----------------------

Then you need to write code to update the records. This can be done with dynamic SQL execution via the Execute or Runsql methods. Yo can also open a record set and use the EDIT and UPDATE methods. There are lots of examples in these forums, in help and online.

-----------------------

Update Example using RunSQL:

Dim sSQL as string

sSQL="Update table1 Set col2='" & me.textbox2 & "', col3='" & me.textbox3 & "' Where recID=" & Me.textbox1
DoCmd.RunSQL(sSQL)

-----------------------

Update example using EDIT and UPDATE methods with recordset:

Sub UpdateTable1()

Dim dbsMyDB As Database
Dim rsttable1 As Recordset
Dim sSQL As String
sSQL="Select * From Table1 Where RecID=" & textbox1

Set dbsMyDB = OpenDatabase("MyDB.mdb&quot
Set rsttable1 = dbsMyDB.OpenRecordset(sSQL, dbOpenDynaset)

With rsttable1
.Edit
!col2 = textbox2
!col3 = textbox3
!col4 = textbox4
.Update
End With

rsttable1.Close
dbsMyDB.Close

End Sub

-----------------------

NOTE: I haven't tested these examples so they may contain errors but should be sufficient to give you an idea how to proceed. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[Scott2S]

Wow, this code snippet you posted:

Private Sub RecordsListbox_AfterUpdate()
Me.RecordsetClone.FindFirst "[RecID] = " & Me.RecordsListbox
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

works wonderfull! Thanks again! How did you know to use the recordsetclone and bookmark? I have never used these before.

 

[tlbroadbent]

I first encountered RecordsetClone and Bookmark years ago but haven't used them much. The code snippet was actually based on code originally found at MVPS.ORG.

http://www.mvps.org/access/forms/frm0005.htm

Access Help contains a similar example. The RecordsetClone and Bookmark properties are explained in Access Help. Here are a couple of online links.

http://msdn.microsoft.com/library/en-us/vbaac10/html/acproRecordsetClone.asp

http://msdn.microsoft.com/library/en-us/vbaac10/html/acproBookmark.asp

Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[Aivars]

Finding record on subform:

private sub cboComboBox_AfterUpdate()
dim rst as recordset

set rst=me.subform.recordsetclone
'Use identifiers ' for text or # for date type field
rst.findfirst "MyField=" & me.cboComboBox
if not rst.nomatch then
me.subform.bookmark=rst.bookmark
end if
rst.close
set rst=nothing
end sub

Aivars

 

[Scott2S]

OK. So far this has been great, but I went to try this on a table that has it's uniqueness spanned across two columns. How do I set my bookmark on the exact match of the record instead of just finding the first match?
Thanks in advance