UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Using A Recordset To Return A Value For Control On A Form, Access 2010    
 
   
Clemensn2000
post May 28 2020, 07:54 PM
Post#1



Posts: 39
Joined: 6-January 06



I'm using Access 2010. I am developing a form bound to table A and need to generate a value for one of the key fields from a related table (call it table B). The condition is that I need to return the ID of the record in Table B where the inception date in Table A lies between the From date and the To date fields in Table B. I'll then use the ID as an entry in the form.I think I will need to use some recordset VBA coding here, but I'm struggling as to where to start. Can some one point me in the right direction.
Many thanks
Neil
Go to the top of the page
 
RJD
post May 28 2020, 08:22 PM
Post#2


UtterAccess VIP
Posts: 10,584
Joined: 25-October 10
From: Gulf South USA


Hi: If you need to edit the form controls bound to fields in tblA, then you could do this as the form record source ...

CODE
SELECT tblA.ID, tblA.InceptionDate,
DLookUp("[tblB_ID]","[tblB]","#" & [InceptionDate] & "# Between [FromDate] And [ToDate]") AS TblB_ID
FROM tblA;


If you do not need to edit the tblA controls on the form, then you could use this ...

CODE
SELECT tblA.ID, tblA.InceptionDate, tblB.tblB_ID
FROM tblA LEFT JOIN tblB ON (tblA.InceptionDate <= tblB.ToDate) AND (tblA.InceptionDate >= TblB.FromDate);


See the demo attached, with the form using the first query.

HTH
Joe

Attached File(s)
Attached File  ReturnAValue.zip ( 21.77K )Number of downloads: 1
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Clemensn2000
post May 28 2020, 08:38 PM
Post#3



Posts: 39
Joined: 6-January 06



Many thanks for the prompt response Joe, I'll give a try in a while and let you know how I get on. Looks straight forward.
Neil
Go to the top of the page
 
RJD
post May 28 2020, 08:42 PM
Post#4


UtterAccess VIP
Posts: 10,584
Joined: 25-October 10
From: Gulf South USA


You are welcome, Neil. Let us know how this works for you, or if we need to investigate this further.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Clemensn2000
post May 28 2020, 08:44 PM
Post#5



Posts: 39
Joined: 6-January 06



Joe,
The problem with your solution is that the form you've produced (which would be the equivalent of the form I'm working on is bound to your query while my form is already bound to table A? Or perhaps I'm being a bit dim...
Neil
Go to the top of the page
 
RJD
post May 28 2020, 08:48 PM
Post#6


UtterAccess VIP
Posts: 10,584
Joined: 25-October 10
From: Gulf South USA


Hi: You can change the record source to use the query instead of the table - does the same thing and allows more flexibility as well. Or you could use the table (not my preferred method) and put the DLookup in a textbox on the form, associated with each record.

=DLookUp("[tblB_ID]","[tblB]","#" & [InceptionDate] & "# Between [FromDate] And [ToDate]") AS TblB_ID

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
RJD
post May 28 2020, 08:56 PM
Post#7


UtterAccess VIP
Posts: 10,584
Joined: 25-October 10
From: Gulf South USA


Neil: In case you are stuck on the table approach (again, not my preferred method) see the revised demo attached using the table record source and a form textbox using the DLookup.

HTH
Joe
Attached File(s)
Attached File  ReturnAValue_Rev1.zip ( 23.78K )Number of downloads: 2
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Clemensn2000
post May 28 2020, 09:06 PM
Post#8



Posts: 39
Joined: 6-January 06



OK Joe I'll give that a try (it'll have to be tomorrow now). I agree with you on the table approach and I usually bind my forms to queries, but made an exception in this case and am too far along to want to change it if I can avoid it. I think the problem I'm going to have if I use the query approach is that the join to table B is the metric I'm trying to establish with the code for the current record so I'm not sure if that would work (although I may be a bit confused here...).
Thanks again
Neil
Go to the top of the page
 
RJD
post May 28 2020, 09:42 PM
Post#9


UtterAccess VIP
Posts: 10,584
Joined: 25-October 10
From: Gulf South USA


Neil: I gave you three examples ... using a query that contains a DLookup to get the tblB value you want so you can show it on the form, using a query that links to tblB using the date range against the inception date, and using a table to feed the form, but using a textbox with DLookup to find the additional value you want (the last demo - Rev1). Two of these allow edits of tblA records, and one does not.

Using a query as the form record source is easy. Just make the query and substitute that name as the record source for the form.

What else do you see as a requirement?

Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Clemensn2000
post May 29 2020, 08:51 AM
Post#10



Posts: 39
Joined: 6-January 06



Joe,
The query worked perfectly - I think I'm good to go from here. Many thanks, excellent advice.
Cheers
Neil
Go to the top of the page
 
RJD
post May 29 2020, 09:49 AM
Post#11


UtterAccess VIP
Posts: 10,584
Joined: 25-October 10
From: Gulf South USA


You are very welcome, Neil. Continued success ...

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th July 2020 - 06:12 AM