By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,226 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

Auto fill field from value in Report

100+
P: 147
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:
Expand|Select|Wrap|Line Numbers
  1. Dim stDocName As String
  2.  
  3. stDocName = "frmAssignEquip"
  4. DoCmd.OpenForm stDocName
  5. DoCmd.Close acReport, "rptAssignedEquip"
Thanks for any help
Dan
Jan 26 '08 #1
Share this Question
Share on Google+
9 Replies


Jim Doherty
Expert 100+
P: 897
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
Jan 26 '08 #2

100+
P: 147
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
Jan 26 '08 #3

missinglinq
Expert 2.5K+
P: 3,532
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)>
Jan 26 '08 #4

100+
P: 147
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.
Jan 26 '08 #5

100+
P: 147
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!!!!!!
Jan 26 '08 #6

Jim Doherty
Expert 100+
P: 897
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...

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. 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

Expand|Select|Wrap|Line Numbers
  1.  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

Expand|Select|Wrap|Line Numbers
  1.  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

Expand|Select|Wrap|Line Numbers
  1. 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

Expand|Select|Wrap|Line Numbers
  1.  Dim stLinkCriteria 
  2. stLinkCriteria= "[ID]=" & Me!ID
  3. DoCmd.OpenForm "frmAssignEquip",,,strLinkCriteria
  4. Forms!frmAssignEquip![Name]=Me![Name]
  5. DoCmd.Close acReport Me.Name, acSaveNo
  6.  

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 :)
Jan 27 '08 #7

NeoPa
Expert Mod 15k+
P: 31,277
Subscribing .
Feb 2 '08 #8

100+
P: 147
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...

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. 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

Expand|Select|Wrap|Line Numbers
  1.  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

Expand|Select|Wrap|Line Numbers
  1.  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

Expand|Select|Wrap|Line Numbers
  1. 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

Expand|Select|Wrap|Line Numbers
  1.  Dim stLinkCriteria 
  2. stLinkCriteria= "[ID]=" & Me!ID
  3. DoCmd.OpenForm "frmAssignEquip",,,strLinkCriteria
  4. Forms!frmAssignEquip![Name]=Me![Name]
  5. DoCmd.Close acReport Me.Name, acSaveNo
  6.  

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.
Feb 4 '08 #9

Jim Doherty
Expert 100+
P: 897
Jim,

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

Jim :)
Feb 4 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.