Auto fill field from value in Report

[DAHMB]

I have a Report in which I have one of the fields set up to launch a form with the On Click event. I would like to have the form launch and auto fill a field with the value of the field clicked in the report. For example in the report I click the name John Jones in the Name field, the form launches and I want the name John Jones entered in the Name field of the report.

Any ideas So far I have this in the on click event of the field in the report:

Dim stDocName As String
 
stDocName = "frmAssignEquip"
DoCmd.OpenForm stDocName
DoCmd.Close acReport, "rptAssignedEquip"

Thanks for any help
Dan

[Jim Doherty]

I have a Report in which I have one of the fields set up to launch a form with the On Click event. I would like to have the form launch and auto fill a field with the value of the field clicked in the report. For example in the report I click the name John Jones in the Name field, the form launches and I want the name John Jones entered in the Name field of the report.

Any ideas So far I have this in the on click event of the field in the report:

1. Dim stDocName As String

2. stDocName = "frmAssignEquip"
3. DoCmd.OpenForm stDocName
4. DoCmd.Close acReport, "rptAssignedEquip"


Thanks for any help
Dan

Dan,

Slightly confusing..reports don't have an on click event! are you referring to a form here?

Jim

[DAHMB]

Dan,

Slightly confusing..reports don't have an on click event! are you referring to a form here?

Jim

Hi,
Yes it is definately a report. I am using Access 2007 maybe it is new to this version. But I would asume it would work the same in a report as in a form (hopefully). Any ideas?
Thanks
Dan

[missinglinq]

Exactly how would you "click" on a textbox on a Report, using a paper mouse perhaps? I'm sorry, Dan, but Jim is correct! Access 2007 has a lot of new features, but controls on Reports with Click events are not among them!

Linq ;0)>

[DAHMB]

Exactly how would you "click" on a textbox on a Report, using a paper mouse perhaps? I'm sorry, Dan, but Jim is correct! Access 2007 has a lot of new features, but controls on Reports with Click events are not among them!

Linq ;0)>

Missingling you are wrong and being sarcastic is only makes you sound worse. I have a Report in Access 2007, in the report I have a text box and under Event of the text box there is an on click feature. I have it set up to open a form and it works. That being said does anyone have an answer to my original question? Please solutions only I don't have time for negatives.

[DAHMB]

Missingling you are wrong and being sarcastic is only makes you sound worse. I have a Report in Access 2007, in the report I have a text box and under Event of the text box there is an on click feature. I have it set up to open a form and it works. That being said does anyone have an answer to my original question? Please solutions only I don't have time for negatives.

As proof to all the doubters view the MS Access 2007 student Sample database!!!!!!

[Jim Doherty]

Hi,
Yes it is definately a report. I am using Access 2007 maybe it is new to this version. But I would asume it would work the same in a report as in a form (hopefully). Any ideas?
Thanks
Dan

OK Dan... well you caught me on that one I don't personally use the new Access 2007 version (support all previous versions but not the new one just yet ) events for textboxes in reports for Access 2007 are indeed there for on click (and others) so you are right and I am wrong. I apologise for not knowing this you have now taught me something.

Anyway,,,, the principles will be the same for the purposes of navigating around a system in that you can open a form from a form and also now in Access 2007 a form from a report with an 'on click' event. So how would we do that? well in much the same way as we do with form to form in that you would define some criteria to establish a link between the record you are clicking in the report and the record you wish to see in the form

So...

Dim stLinkCriteria

will define a variable of variant to store the value we wish to use in the navigation sequence (you could define it as a string or number type if we know the data type of the field we are using) but lets say for the moment that the field we are going to use is called ID

We then define what that stLinkCriteria will be by stating it thus:

stLinkCriteria= "[ID]=" & Me!ID

Me!ID relates to the field on the report called ID and the left side of the equals sign is the field on the form we intend opening that is called ID also

We then issue the command passing WHERE clause criteria to the openform command so that the form opens to the relevant record we wish to see

 DoCmd.OpenForm "frmAssignEquip",,,strLinkCriteria

The idea then is to pass the value of the 'Name' field you mention to the corresponding 'Name' field on the form which is done like this

 Forms!frmAssignEquip![Name]=Me![Name]

Once the value has been passed we can then close the report (which is still open at this point) leaving only the form itself open

DoCmd.Close acReport Me.Name, acSaveNo

(The acSaveNo segment of the close action on the last line tells access to close the report and disregard any changes that you might have made within the body of the report itself at some time during the time it has been currently open without prompting the user. Incidentally you can refer to the 'name' property of the report itself explicitly if your code is behind the report from which your code is executing.....Me.Name)

So revising this then to achieve what you need... the entire code block will be this

 Dim stLinkCriteria 
stLinkCriteria= "[ID]=" & Me!ID
DoCmd.OpenForm "frmAssignEquip",,,strLinkCriteria
Forms!frmAssignEquip![Name]=Me![Name]
DoCmd.Close acReport Me.Name, acSaveNo
 

As an additional it is never a good idea to use words describing fields that Access likes to keep to itself 'reserved' words in other words for naming fields. You are obliged to wrap them in square brackets in much the same way as if you have spaces in field names. It just makes for extra work for yourself and others who might have to read syntax strings where you see some having brackets and others not....its a general standard I'm talking about here not an obligation.


Kind Regards

Jim :)

[NeoPa]

Subscribing .

[DAHMB]

OK Dan... well you caught me on that one I don't personally use the new Access 2007 version (support all previous versions but not the new one just yet ) events for textboxes in reports for Access 2007 are indeed there for on click (and others) so you are right and I am wrong. I apologise for not knowing this you have now taught me something.

Anyway,,,, the principles will be the same for the purposes of navigating around a system in that you can open a form from a form and also now in Access 2007 a form from a report with an 'on click' event. So how would we do that? well in much the same way as we do with form to form in that you would define some criteria to establish a link between the record you are clicking in the report and the record you wish to see in the form

So...

Dim stLinkCriteria

will define a variable of variant to store the value we wish to use in the navigation sequence (you could define it as a string or number type if we know the data type of the field we are using) but lets say for the moment that the field we are going to use is called ID

We then define what that stLinkCriteria will be by stating it thus:

stLinkCriteria= "[ID]=" & Me!ID

Me!ID relates to the field on the report called ID and the left side of the equals sign is the field on the form we intend opening that is called ID also

We then issue the command passing WHERE clause criteria to the openform command so that the form opens to the relevant record we wish to see

 DoCmd.OpenForm "frmAssignEquip",,,strLinkCriteria

The idea then is to pass the value of the 'Name' field you mention to the corresponding 'Name' field on the form which is done like this

 Forms!frmAssignEquip![Name]=Me![Name]

Once the value has been passed we can then close the report (which is still open at this point) leaving only the form itself open

DoCmd.Close acReport Me.Name, acSaveNo

(The acSaveNo segment of the close action on the last line tells access to close the report and disregard any changes that you might have made within the body of the report itself at some time during the time it has been currently open without prompting the user. Incidentally you can refer to the 'name' property of the report itself explicitly if your code is behind the report from which your code is executing.....Me.Name)

So revising this then to achieve what you need... the entire code block will be this

 Dim stLinkCriteria 
stLinkCriteria= "[ID]=" & Me!ID
DoCmd.OpenForm "frmAssignEquip",,,strLinkCriteria
Forms!frmAssignEquip![Name]=Me![Name]
DoCmd.Close acReport Me.Name, acSaveNo
 

As an additional it is never a good idea to use words describing fields that Access likes to keep to itself 'reserved' words in other words for naming fields. You are obliged to wrap them in square brackets in much the same way as if you have spaces in field names. It just makes for extra work for yourself and others who might have to read syntax strings where you see some having brackets and others not....its a general standard I'm talking about here not an obligation.


Kind Regards

Jim :)

Jim,

Thank you very much that was it!!!!!! Sorry for the delayed thanks I was away.

[Jim Doherty]

Jim,

Thank you very much that was it!!!!!! Sorry for the delayed thanks I was away.

You,re welcome

Jim :)