Goto Related record

Hi,

I am new to forms and macro's etc so apologies in advance if there is a
simple solution to this.

I have a basic requirement where a form shows a related record number e.g.
a students record displays the ID of a related student - in my case this
refers to the primary record where aditional details are stored - address,
parents / guardians etc. I want to place a button on a form that will allow
the user to click the button to jump to the related record.

Any assistance would be greatly appreciated.

Thanks in advance.
Kirk

A command button might work for you. Use the command button wizard for goto
record. If not you can use a combo box for a filter to find records. Use the
combo box wizard to create the combo and let it use the field name for your
lookup. Then right click on the combo, go to properties and then events. In
the afterupdate event start the code builder and type:
Me.filter = "[yourfieldname] = """ & Me.combo# & """"
Me.filteron=true

"IKMD66" wrote:

Hi,

I am new to forms and macro's etc so apologies in advance if there is a
simple solution to this.

I have a basic requirement where a form shows a related record number e.g.
a students record displays the ID of a related student - in my case this
refers to the primary record where aditional details are stored - address,
parents / guardians etc. I want to place a button on a form that will allow
the user to click the button to jump to the related record.

Any assistance would be greatly appreciated.

Thanks in advance.
Kirk

Hi,

Thanks for your reply. I already have a go to (combo box) on the header of
the form - where the user can select the record and the form jumps to the
record. The issue I am trying to solve is - within a record there may already
be maintained a related record number I require a button to be placed beside
the related record number field that will allow the user simply to press the
button and jump to the related record (without having to type anything).

Further assistance is appreciiated.

Regards,
Kirk

"Golfinray" wrote:

A command button might work for you. Use the command button wizard for goto
record. If not you can use a combo box for a filter to find records. Use the
combo box wizard to create the combo and let it use the field name for your
lookup. Then right click on the combo, go to properties and then events. In
the afterupdate event start the code builder and type:
Me.filter = "[yourfieldname] = """ & Me.combo# & """"
Me.filteron=true

"IKMD66" wrote:

Hi,

I am new to forms and macro's etc so apologies in advance if there is a
simple solution to this.

I have a basic requirement where a form shows a related record number e.g.
a students record displays the ID of a related student - in my case this
refers to the primary record where aditional details are stored - address,
parents / guardians etc. I want to place a button on a form that will allow
the user to click the button to jump to the related record.

Any assistance would be greatly appreciated.

Thanks in advance.
Kirk

When you say "jump to the related record" do you mean you're trying to
open another form to show additional info for that record ID? If so, you
can use the OpenArgs property of the OpenForm method.

In the On Click event of your command button in the first form put code like;

DoCmd.OpenForm "YourOtherForm", , , , , , "[RecordID]=" & Me.RecordID

(make sure to use the correct number of commas)

Then in the Open event of your other form you would put:

If Not IsNull (Me.OpenArgs) Then
Me.Filter = Me.OpenArgs
Me.FilterOn = True
End If
--
_________

Sean Bailey


"IKMD66" wrote:

Hi,

Thanks for your reply. I already have a go to (combo box) on the header of
the form - where the user can select the record and the form jumps to the
record. The issue I am trying to solve is - within a record there may already
be maintained a related record number I require a button to be placed beside
the related record number field that will allow the user simply to press the
button and jump to the related record (without having to type anything).

Further assistance is appreciiated.

Regards,
Kirk

"Golfinray" wrote:

A command button might work for you. Use the command button wizard for goto
record. If not you can use a combo box for a filter to find records. Use the
combo box wizard to create the combo and let it use the field name for your
lookup. Then right click on the combo, go to properties and then events. In
the afterupdate event start the code builder and type:
Me.filter = "[yourfieldname] = """ & Me.combo# & """"
Me.filteron=true

"IKMD66" wrote:

Hi,

I am new to forms and macro's etc so apologies in advance if there is a
simple solution to this.

I have a basic requirement where a form shows a related record number e.g.
a students record displays the ID of a related student - in my case this
refers to the primary record where aditional details are stored - address,
parents / guardians etc. I want to place a button on a form that will allow
the user to click the button to jump to the related record.

Any assistance would be greatly appreciated.

Thanks in advance.
Kirk

Hi,

Thansk foryour response.

Yes I do mean that it should open the (same) form but for the related
record. I will try what you suggest.

Regards,
Kirk

"Beetle" wrote:

When you say "jump to the related record" do you mean you're trying to
open another form to show additional info for that record ID? If so, you
can use the OpenArgs property of the OpenForm method.

In the On Click event of your command button in the first form put code like;

DoCmd.OpenForm "YourOtherForm", , , , , , "[RecordID]=" & Me.RecordID

(make sure to use the correct number of commas)

Then in the Open event of your other form you would put:

If Not IsNull (Me.OpenArgs) Then
Me.Filter = Me.OpenArgs
Me.FilterOn = True
End If
--
_________

Sean Bailey


"IKMD66" wrote:

Hi,

Thanks for your reply. I already have a go to (combo box) on the header of
the form - where the user can select the record and the form jumps to the
record. The issue I am trying to solve is - within a record there may already
be maintained a related record number I require a button to be placed beside
the related record number field that will allow the user simply to press the
button and jump to the related record (without having to type anything).

Further assistance is appreciiated.

Regards,
Kirk

"Golfinray" wrote:

A command button might work for you. Use the command button wizard for goto
record. If not you can use a combo box for a filter to find records. Use the
combo box wizard to create the combo and let it use the field name for your
lookup. Then right click on the combo, go to properties and then events. In
the afterupdate event start the code builder and type:
Me.filter = "[yourfieldname] = """ & Me.combo# & """"
Me.filteron=true

"IKMD66" wrote:

Hi,

I am new to forms and macro's etc so apologies in advance if there is a
simple solution to this.

I have a basic requirement where a form shows a related record number e.g.
a students record displays the ID of a related student - in my case this
refers to the primary record where aditional details are stored - address,
parents / guardians etc. I want to place a button on a form that will allow
the user to click the button to jump to the related record.

Any assistance would be greatly appreciated.

Thanks in advance.
Kirk

The method I described is to open a differnet form based on some criteria.
However, I now suspect that you are just trying to move to a different
record on the same form, in which case my previous suggestion will not
work. If you are just trying to move to a different record on the same form,
then post back.
--
_________

Sean Bailey


"IKMD66" wrote:

Hi,

Thansk foryour response.

Yes I do mean that it should open the (same) form but for the related
record. I will try what you suggest.

Regards,
Kirk

"Beetle" wrote:

When you say "jump to the related record" do you mean you're trying to
open another form to show additional info for that record ID? If so, you
can use the OpenArgs property of the OpenForm method.

In the On Click event of your command button in the first form put code like;

DoCmd.OpenForm "YourOtherForm", , , , , , "[RecordID]=" & Me.RecordID

(make sure to use the correct number of commas)

Then in the Open event of your other form you would put:

If Not IsNull (Me.OpenArgs) Then
Me.Filter = Me.OpenArgs
Me.FilterOn = True
End If
--
_________

Sean Bailey


"IKMD66" wrote:

Hi,

Thanks for your reply. I already have a go to (combo box) on the header of
the form - where the user can select the record and the form jumps to the
record. The issue I am trying to solve is - within a record there may already
be maintained a related record number I require a button to be placed beside
the related record number field that will allow the user simply to press the
button and jump to the related record (without having to type anything).

Further assistance is appreciiated.

Regards,
Kirk

"Golfinray" wrote:

A command button might work for you. Use the command button wizard for goto
record. If not you can use a combo box for a filter to find records. Use the
combo box wizard to create the combo and let it use the field name for your
lookup. Then right click on the combo, go to properties and then events. In
the afterupdate event start the code builder and type:
Me.filter = "[yourfieldname] = """ & Me.combo# & """"
Me.filteron=true

"IKMD66" wrote:

Hi,

I am new to forms and macro's etc so apologies in advance if there is a
simple solution to this.

I have a basic requirement where a form shows a related record number e.g.
a students record displays the ID of a related student - in my case this
refers to the primary record where aditional details are stored - address,
parents / guardians etc. I want to place a button on a form that will allow
the user to click the button to jump to the related record.

Any assistance would be greatly appreciated.

Thanks in advance.
Kirk

Hi Sean,

Yes I am just trying to move to a different record on the same form e.g when
I view client record 25, recorded within this record is the related client ID
10. I want to place a button beside this field that allows me to go to record
10.

Thanks again.

Regards,
Kirk

"Beetle" wrote:

The method I described is to open a differnet form based on some criteria.
However, I now suspect that you are just trying to move to a different
record on the same form, in which case my previous suggestion will not
work. If you are just trying to move to a different record on the same form,
then post back.
--
_________

Sean Bailey


"IKMD66" wrote:

Hi,

Thansk foryour response.

Yes I do mean that it should open the (same) form but for the related
record. I will try what you suggest.

Regards,
Kirk

"Beetle" wrote:

When you say "jump to the related record" do you mean you're trying to
open another form to show additional info for that record ID? If so, you
can use the OpenArgs property of the OpenForm method.

In the On Click event of your command button in the first form put code like;

DoCmd.OpenForm "YourOtherForm", , , , , , "[RecordID]=" & Me.RecordID

(make sure to use the correct number of commas)

Then in the Open event of your other form you would put:

If Not IsNull (Me.OpenArgs) Then
Me.Filter = Me.OpenArgs
Me.FilterOn = True
End If
--
_________

Sean Bailey


"IKMD66" wrote:

Hi,

Thanks for your reply. I already have a go to (combo box) on the header of
the form - where the user can select the record and the form jumps to the
record. The issue I am trying to solve is - within a record there may already
be maintained a related record number I require a button to be placed beside
the related record number field that will allow the user simply to press the
button and jump to the related record (without having to type anything).

Further assistance is appreciiated.

Regards,
Kirk

"Golfinray" wrote:

A command button might work for you. Use the command button wizard for goto
record. If not you can use a combo box for a filter to find records. Use the
combo box wizard to create the combo and let it use the field name for your
lookup. Then right click on the combo, go to properties and then events. In
the afterupdate event start the code builder and type:
Me.filter = "[yourfieldname] = """ & Me.combo# & """"
Me.filteron=true

"IKMD66" wrote:

Hi,

I am new to forms and macro's etc so apologies in advance if there is a
simple solution to this.

I have a basic requirement where a form shows a related record number e.g.
a students record displays the ID of a related student - in my case this
refers to the primary record where aditional details are stored - address,
parents / guardians etc. I want to place a button on a form that will allow
the user to click the button to jump to the related record.

Any assistance would be greatly appreciated.

Thanks in advance.
Kirk

OK, so let's suppose you have a field called ClientID that is the primary key
for each client, and another field called RelatedClient that holds the
ClientID
number for some other client, each with an associated text box control on
your form. All you should need is some code like this in the On Click event
of your button;

Private Sub cmdYourButton_Click()

With Me.RecordsetClone
.FindFirst "[ClientID]=" & Me![RelatedClient]
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

The above code assumes that ClientID and RelatedClient are integer values.
If they are text, then you will need to add qoutes in the code.
--
_________

Sean Bailey


"IKMD66" wrote:

Hi Sean,

Yes I am just trying to move to a different record on the same form e.g when
I view client record 25, recorded within this record is the related client ID
10. I want to place a button beside this field that allows me to go to record
10.

Thanks again.

Regards,
Kirk

"Beetle" wrote:

The method I described is to open a differnet form based on some criteria.
However, I now suspect that you are just trying to move to a different
record on the same form, in which case my previous suggestion will not
work. If you are just trying to move to a different record on the same form,
then post back.
--
_________

Sean Bailey


"IKMD66" wrote:

Hi,

Thansk foryour response.

Yes I do mean that it should open the (same) form but for the related
record. I will try what you suggest.

Regards,
Kirk

"Beetle" wrote:

When you say "jump to the related record" do you mean you're trying to
open another form to show additional info for that record ID? If so, you
can use the OpenArgs property of the OpenForm method.

In the On Click event of your command button in the first form put code like;

DoCmd.OpenForm "YourOtherForm", , , , , , "[RecordID]=" & Me.RecordID

(make sure to use the correct number of commas)

Then in the Open event of your other form you would put:

If Not IsNull (Me.OpenArgs) Then
Me.Filter = Me.OpenArgs
Me.FilterOn = True
End If
--
_________

Sean Bailey


"IKMD66" wrote:

Hi,

Thanks for your reply. I already have a go to (combo box) on the header of
the form - where the user can select the record and the form jumps to the
record. The issue I am trying to solve is - within a record there may already
be maintained a related record number I require a button to be placed beside
the related record number field that will allow the user simply to press the
button and jump to the related record (without having to type anything).

Further assistance is appreciiated.

Regards,
Kirk

"Golfinray" wrote:

A command button might work for you. Use the command button wizard for goto
record. If not you can use a combo box for a filter to find records. Use the
combo box wizard to create the combo and let it use the field name for your
lookup. Then right click on the combo, go to properties and then events. In
the afterupdate event start the code builder and type:
Me.filter = "[yourfieldname] = """ & Me.combo# & """"
Me.filteron=true

"IKMD66" wrote:

Hi,

I am new to forms and macro's etc so apologies in advance if there is a
simple solution to this.

I have a basic requirement where a form shows a related record number e.g.
a students record displays the ID of a related student - in my case this
refers to the primary record where aditional details are stored - address,
parents / guardians etc. I want to place a button on a form that will allow
the user to click the button to jump to the related record.

Any assistance would be greatly appreciated.

Thanks in advance.
Kirk

Sean - You're a star.

Many thanks for your assitance - your logic provides exactly what is required.

Best Regards,
Kirk

"Beetle" wrote:

OK, so let's suppose you have a field called ClientID that is the primary key
for each client, and another field called RelatedClient that holds the
ClientID
number for some other client, each with an associated text box control on
your form. All you should need is some code like this in the On Click event
of your button;

Private Sub cmdYourButton_Click()

With Me.RecordsetClone
.FindFirst "[ClientID]=" & Me![RelatedClient]
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

The above code assumes that ClientID and RelatedClient are integer values.
If they are text, then you will need to add qoutes in the code.
--
_________

Sean Bailey


"IKMD66" wrote:

Hi Sean,

Yes I am just trying to move to a different record on the same form e.g when
I view client record 25, recorded within this record is the related client ID
10. I want to place a button beside this field that allows me to go to record
10.

Thanks again.

Regards,
Kirk

"Beetle" wrote:

The method I described is to open a differnet form based on some criteria.
However, I now suspect that you are just trying to move to a different
record on the same form, in which case my previous suggestion will not
work. If you are just trying to move to a different record on the same form,
then post back.
--
_________

Sean Bailey


"IKMD66" wrote:

Hi,

Thansk foryour response.

Yes I do mean that it should open the (same) form but for the related
record. I will try what you suggest.

Regards,
Kirk

"Beetle" wrote:

When you say "jump to the related record" do you mean you're trying to
open another form to show additional info for that record ID? If so, you
can use the OpenArgs property of the OpenForm method.

In the On Click event of your command button in the first form put code like;

DoCmd.OpenForm "YourOtherForm", , , , , , "[RecordID]=" & Me.RecordID

(make sure to use the correct number of commas)

Then in the Open event of your other form you would put:

If Not IsNull (Me.OpenArgs) Then
Me.Filter = Me.OpenArgs
Me.FilterOn = True
End If
--
_________

Sean Bailey


"IKMD66" wrote:

Hi,

Thanks for your reply. I already have a go to (combo box) on the header of
the form - where the user can select the record and the form jumps to the
record. The issue I am trying to solve is - within a record there may already
be maintained a related record number I require a button to be placed beside
the related record number field that will allow the user simply to press the
button and jump to the related record (without having to type anything).

Further assistance is appreciiated.

Regards,
Kirk

"Golfinray" wrote:

A command button might work for you. Use the command button wizard for goto
record. If not you can use a combo box for a filter to find records. Use the
combo box wizard to create the combo and let it use the field name for your
lookup. Then right click on the combo, go to properties and then events. In
the afterupdate event start the code builder and type:
Me.filter = "[yourfieldname] = """ & Me.combo# & """"
Me.filteron=true

"IKMD66" wrote:

Hi,

I am new to forms and macro's etc so apologies in advance if there is a
simple solution to this.

I have a basic requirement where a form shows a related record number e.g.
a students record displays the ID of a related student - in my case this
refers to the primary record where aditional details are stored - address,
parents / guardians etc. I want to place a button on a form that will allow
the user to click the button to jump to the related record.

Any assistance would be greatly appreciated.

Thanks in advance.
Kirk

Sean,

If I may ask one more question. How do I prevent the runtime error "(3077)
syntax error (missing operator) in expression", if the button ie pressed when
no related record is maintained - in this case I do not want anything to
happen - simply, perhaps a message stating "Related Client not maintained."

I have detailed the programming below.

Private Sub GOTO_Click()
With Me.RecordsetClone
..FindFirst "[Client ID]=" & Me![Related (Primary) Client]
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub


Thanks again.
Kirk

"Beetle" wrote:

OK, so let's suppose you have a field called ClientID that is the primary key
for each client, and another field called RelatedClient that holds the
ClientID
number for some other client, each with an associated text box control on
your form. All you should need is some code like this in the On Click event
of your button;

Private Sub cmdYourButton_Click()

With Me.RecordsetClone
.FindFirst "[ClientID]=" & Me![RelatedClient]
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

The above code assumes that ClientID and RelatedClient are integer values.
If they are text, then you will need to add qoutes in the code.
--
_________

Sean Bailey


"IKMD66" wrote:

Hi Sean,

Yes I am just trying to move to a different record on the same form e.g when
I view client record 25, recorded within this record is the related client ID
10. I want to place a button beside this field that allows me to go to record
10.

Thanks again.

Regards,
Kirk

"Beetle" wrote:

The method I described is to open a differnet form based on some criteria.
However, I now suspect that you are just trying to move to a different
record on the same form, in which case my previous suggestion will not
work. If you are just trying to move to a different record on the same form,
then post back.
--
_________

Sean Bailey


"IKMD66" wrote:

Hi,

Thansk foryour response.

Yes I do mean that it should open the (same) form but for the related
record. I will try what you suggest.

Regards,
Kirk

"Beetle" wrote:

When you say "jump to the related record" do you mean you're trying to
open another form to show additional info for that record ID? If so, you
can use the OpenArgs property of the OpenForm method.

In the On Click event of your command button in the first form put code like;

DoCmd.OpenForm "YourOtherForm", , , , , , "[RecordID]=" & Me.RecordID

(make sure to use the correct number of commas)

Then in the Open event of your other form you would put:

If Not IsNull (Me.OpenArgs) Then
Me.Filter = Me.OpenArgs
Me.FilterOn = True
End If
--
_________

Sean Bailey


"IKMD66" wrote:

Hi,

Thanks for your reply. I already have a go to (combo box) on the header of
the form - where the user can select the record and the form jumps to the
record. The issue I am trying to solve is - within a record there may already
be maintained a related record number I require a button to be placed beside
the related record number field that will allow the user simply to press the
button and jump to the related record (without having to type anything).

Further assistance is appreciiated.

Regards,
Kirk

"Golfinray" wrote:

A command button might work for you. Use the command button wizard for goto
record. If not you can use a combo box for a filter to find records. Use the
combo box wizard to create the combo and let it use the field name for your
lookup. Then right click on the combo, go to properties and then events. In
the afterupdate event start the code builder and type:
Me.filter = "[yourfieldname] = """ & Me.combo# & """"
Me.filteron=true

"IKMD66" wrote:

Hi,

I am new to forms and macro's etc so apologies in advance if there is a
simple solution to this.

I have a basic requirement where a form shows a related record number e.g.
a students record displays the ID of a related student - in my case this
refers to the primary record where aditional details are stored - address,
parents / guardians etc. I want to place a button on a form that will allow
the user to click the button to jump to the related record.

Any assistance would be greatly appreciated.

Thanks in advance.
Kirk