Accessing Second Table from a Table-bound Access Form 2

[raabbasi]

How to occess another table for reading and matching the values, while working with a MS Access Form, which is already bound to a table.
A tip on this issue will be highly appreciated.

 

[KenReay]

Hi

Consider

Using a query containing both tables as teh recordsource of the form

AND/OR

Using combo boxes and/or list boxes bound to the original table, but with a rowsource pointing to the other table

Regards

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

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

UK

 

[LittleSmudge]

OR

In the On_Load ( or similar ) event put code that opens an ADODB.Recordset on the second table with the WHERE clause selecting the appropriate record. Then use the data from the recordset to populate unbound text box controls.

Works well if data is read only.
More complex if you need to let the users Add/Edit data as you need to detect changes yourself and then save the changes to the table using another ADODB.Recordset.


'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[raabbasi]

Thanks LittleSmudge for another good idea.

I tried opening ADODB.Recordset. But, it requires opening the ADODB.Connection as well, which is not permitted as the "Data Source" is already open.

Once a Recordset is open, we can manipulate it any way, we want.

It will be a special favor, if you can further elaborate on how to open a Recordset within an Access Form.

 

[LittleSmudge]

IF the table is listed in the tables page of the database then :-

Private Sub PopulateControls
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
rst.ActiveConnection = CurrentProject.Connection
rst.Open "SELECT * FROM tblDemo WHERE DemoId = " & txtDemoRef
If rst.EOF Then ' No Record Found

Else
    ' Code in here to populate the controls.
End If

rst.Close
Set rst = Nothing
End Sub

Where txtDemoRef is a text box control on the main form containing the value of the PK from tblDemo.

Otherwise the rst.ActiveConnection has to link to an ODBC data source to a remote database. ( All the rest stays the same. )

'ope-that-'elps




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[raabbasi]

Salute to LittleSmudge for the great help.

Best regards.