Hi all, This is an easy one ...

[vanleurth]

Hi all,

This is an easy one ... for an expert. Unfortunately, I'm a Dilbereted engineer who's trying to put together a database in Access. I have a form and a datasheet subform.
I want to link the arrow in my datasheet subform to my main form information.
The subform is basically a list of what I have in my table. The Main form only shows information for the actual record.
I would like to connect these two forms; so whenever I select a record in the main form, the record in the datasheet subform will be selected with by the arrow and vicecersa.

So far, I have tried to link the two recordsets (even though it is the same ... ??) Like this;

set Forms!qBookings.frmqCars.Recordset = me.carRecordset

Thank you in advance and happy debugging,
Vanleurth

 

[PaulBricker]

In the subform control you should see a property for Link Master/Link Child. Set these to the fields that are linking the main form with the subform. That should do it.

Paul

 

[vanleurth]

Thank you for your reply,

Actually, I have done this before but now the subform shows me only the record with the same registration ID number.
I would like to see the complete table in my subform, but having the record selector focus on the record that it's shown in the main form.

Perhaps, I should find the property that handle focusing on an record and make it the same as the main's record ID number.

??

Vanleurth

 

[PaulBricker]

Sorry, but I not sure how you would do this. It might be possible with the RecordsetClone and Bookmark properties, but I couldn't find a way.

Paul

 

[JeremyNYC]

Sounds a little funny looking, but maybe not all that bad. In any case, it shouldn't be so hard to do. Here's how I'd start:
-Don't link the form and subform by anything.
-In the Current event of the main form, set the focus to the contron on the subform that holds the PK for this stuff, then do a findFirst.
-Do the same for the Current event of the subform, but this time you'll do the finding in the main form.

Could be kind of cool, though you'll probably want to send the focus back to the place it stared after doing the findfirst, which means recording the location in a control variable.

Take a stab at it and let us know if you have problems.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[vanleurth]

Ok. After almost reading the whole forum and my book, I have come down to this code.

To give you a better overview I'll review and remake the original question.
I have a main form and a subform. The subform is a datasheet which has an arrow for selecting the record. The main form is a normal form which shows information only by record. I want to connect the two without using child/master links since the subform is suppose to show all the data as a table from which both forms are linked.

In my main form I has written;

Private Sub Form_Current()
Dim rst As DAO.Recordset
Dim strSearchName As String

' Set the record set to the subform's record set
Set rst = Forms![frm training schedule]![frm training schedule calendar sub].Recordset

' Check if it is the right record. Focus the subforms' record set.
MsgBox (Forms![frm training schedule].trainingschedID)

' Find the recordset in the datasheet subform and make it current.
strSearchName = CStr(Forms![frm training schedule].trainingschedID)
rst.FindFirst ("trainingschedID = " & strSearchName)

If rst.NoMatch Then
MsgBox "Record not found"
End If

rst.Close

End Sub

The problem is that Access doesn't recognize trainingshedID as part of my subform. Therefore, it doesn't want to find the record ID in my subform . ???
I'll keep trying some other tricks but if somebody has an idea, let me know, I'll try it !!

Vanleurth

 

[PaulBricker]

First, change the . to a ! The . is used for properties of the form/control. You want Access to see TrainSchedID as a field so you need the !

Paul

 

[JeremyNYC]

If you're using this to refer to a control on the subform, you'll have a problem
Forms![frm training schedule].trainingschedID

To refer to a control on a subform, you have to do this:
forms!NameOfMainForm!NameOfControlThatHoldsSubform.form!NameOfControlOnSubform.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[vanleurth]

Alright !! Seems that it is about to work. Thanks for the advice and debugging. Only one more problem .. I think my dataset get lost after the first run. For some reason I can't see the records anymore in my subform after the first run.

The one time running code looks like this;

Dim rst As DAO.Recordset
Dim strSearchName As String

' Set the record set to the subform's record set
Set rst = Me.frm_training_schedule_calendar_sub.Form.Recordset

' Check if it is the right record. Focus the subforms' record set.
MsgBox (Me.frm_training_schedule_calendar_sub.Form!trainingschedID)

' Find the recordset in the datasheet subform and make it current.
strSearchName = CStr(Me.frm_training_schedule_calendar_sub.Form!trainingschedID)
rst.FindFirst ("trainingschedID = " & strSearchName)

If rst.NoMatch Then
MsgBox "Record not found"
End If

rst.Close

 

[vanleurth]

IT'S ALIVE, IT'S ALIVEEEE !!!
Yes, thank you very much for all your help, here is the code.
KEYWORDS: syncronize, subforms, forms.

Vanleurth

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

Dim rst As DAO.Recordset
Dim strSearchName As String

' Set the record set to the subform's record set
Set rst = Me.frm_training_schedule_calendar_sub.Form.Recordset

' Check if it is the right record. Focus the subforms' record set.
MsgBox (Me.frm_training_schedule_calendar_sub.Form!trainingschedID)

' Find the recordset in the datasheet subform and make it current.
strSearchName = CStr(Me!trainingschedID)
rst.FindFirst ("trainingschedID = " & strSearchName)

If rst.NoMatch Then
MsgBox "Record not found"
End If

 

[JeremyNYC]

Excellent. Glad it worked out. =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.