Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-20-2019, 03:37 PM   #1
Big D
Newly Registered User
 
Join Date: Jul 2019
Posts: 17
Thanks: 2
Thanked 0 Times in 0 Posts
Big D is on a distinguished road
Copy Last Entered Record with DMAX and DLOOKUP VBA

Greetings,

I sure could use some help with VBA that will make it possible to copy the last entered record using a command button on the database's Task Details Form and applying DMAX and DLOOKUP VBA. I have attached a WinZipped copy of my Scheduling database to help expedite a solution. Thank you in advance for any help I can get to resolve my VBA issue.

Cheers,
Big D
Attached Files
File Type: zip SchedulingDB.zip (894.0 KB, 23 views)

Big D is offline   Reply With Quote
Old 07-20-2019, 03:55 PM   #2
dynamictiger
Newly Registered User
 
Join Date: Feb 2002
Location: Perth
Posts: 261
Thanks: 11
Thanked 1 Time in 1 Post
dynamictiger
Re: Copy Last Entered Record with DMAX and DLOOKUP VBA

What are you wanting to copy exactly the entry in which case grab the taskID or something else?
__________________
Perth, the most isolated capital in the world
dynamictiger is offline   Reply With Quote
Old 07-20-2019, 04:50 PM   #3
Big D
Newly Registered User
 
Join Date: Jul 2019
Posts: 17
Thanks: 2
Thanked 0 Times in 0 Posts
Big D is on a distinguished road
Re: Copy Last Entered Record with DMAX and DLOOKUP VBA

Quote:
Originally Posted by dynamictiger View Post
What are you wanting to copy exactly the entry in which case grab the taskID or something else?
I want to copy all fields except for the AssignedTo field. Once the last entered record's fields are copied to the new record, the cursor should be sitting the the AssignedTo field and the user has only to select a name from the AssignedTo drop-down menu. All the other fields will already have the date from the last entered record in them.

Please see VBA code in the file. You can see that I used DMAX to grab the last entered record based on the TaskID field which is automatically populated by Access. Then I used DoCmd.GoToRecord , , acNewRec to open a new record and populate all the fields by repeatedly using DLookup.

The cursor should be sitting in the empty AssignedTo field since I used AssignedTo.SetFocus in the last line of the VBA code. Thanks very much for looking into my problem. I'm looking forward to your reply.

Cheers,
Big D

Big D is offline   Reply With Quote
Old 07-20-2019, 05:23 PM   #4
moke123
Me.Dirty=True
 
moke123's Avatar
 
Join Date: Jan 2013
Location: Massachusetts
Posts: 722
Thanks: 1
Thanked 221 Times in 207 Posts
moke123 will become famous soon enough
Re: Copy Last Entered Record with DMAX and DLOOKUP VBA

You should consider getting rid of the lookup fields in your tables, as well as the attachment fields. Also be sure to add option explicit to the declarations at the top of each module.
moke123 is offline   Reply With Quote
The Following User Says Thank You to moke123 For This Useful Post:
Big D (07-20-2019)
Old 07-20-2019, 06:45 PM   #5
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 794
Thanks: 10
Thanked 161 Times in 155 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Copy Last Entered Record with DMAX and DLOOKUP VBA

I think you are basically beat because of the design. First, you form is based on 2 related tables and your record is a parent/child setup. Thus you cannot simply copy and append a record to this query because there will be no parent ID with which to relate the child record. Second, you cannot append all fields because some are calculated, thus they are read only. Lastly (for me anyway) several fields are lookup fields as already noted. These display what looks like normal data, but hide the fact that while you see "Joe Black" the value stored there is (e.g.) 22. IMHO you have made several critical design faux pas' and really ought to bite the bullet and fix them or you'll likely be posting here an awful lot. The issue is that those who are best qualified to help would likely never do these things so it makes it harder to know how to make them work for you. In the end, if you stick with what you have, you might make some progress if you convert the form to a form/subform design.
Maybe take a look at a few links?

Normalization Parts I, II, III, IV, and V http://rogersaccessblog.blogspot.com...on-part-i.html
and/or http://holowczak.com/database-normalization/
Entity-Relationship Diagramming: Part I, II, III and IV http://rogersaccessblog.blogspot.ca/...ng-part-i.html
How do I Create an Application in Microsoft Access? http://rogersaccessblog.blogspot.ca/...cation-in.html
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers - http://www.utteraccess.com/wiki/Autonumbers - http://access.mvps.org/access/general/gen0025.htm
The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - http://www.mendipdatasystems.co.uk/m...lds/4594468763
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
The Following User Says Thank You to Micron For This Useful Post:
Big D (07-20-2019)
Old 07-20-2019, 09:11 PM   #6
Big D
Newly Registered User
 
Join Date: Jul 2019
Posts: 17
Thanks: 2
Thanked 0 Times in 0 Posts
Big D is on a distinguished road
Re: Copy Last Entered Record with DMAX and DLOOKUP VBA

Quote:
Originally Posted by Micron View Post
I think you are basically beat because of the design. First, you form is based on 2 related tables and your record is a parent/child setup. Thus you cannot simply copy and append a record to this query because there will be no parent ID with which to relate the child record. Second, you cannot append all fields because some are calculated, thus they are read only. Lastly (for me anyway) several fields are lookup fields as already noted. These display what looks like normal data, but hide the fact that while you see "Joe Black" the value stored there is (e.g.) 22. IMHO you have made several critical design faux pas' and really ought to bite the bullet and fix them or you'll likely be posting here an awful lot. The issue is that those who are best qualified to help would likely never do these things so it makes it harder to know how to make them work for you. In the end, if you stick with what you have, you might make some progress if you convert the form to a form/subform design.
Maybe take a look at a few links?

Normalization Parts I, II, III, IV, and V http://rogersaccessblog.blogspot.com...on-part-i.html
and/or http://holowczak.com/database-normalization/
Entity-Relationship Diagramming: Part I, II, III and IV http://rogersaccessblog.blogspot.ca/...ng-part-i.html
How do I Create an Application in Microsoft Access? http://rogersaccessblog.blogspot.ca/...cation-in.html
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers - http://www.utteraccess.com/wiki/Autonumbers - http://access.mvps.org/access/general/gen0025.htm
The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - http://www.mendipdatasystems.co.uk/m...lds/4594468763
Thanks very much for your feedback. Greatly appreciated. Do you know if it's possible to select two or more names in the AssignedTo field and have this reflected in the reports? This would solve the problem of not being able to assign more than one person to a task. I actually attempted to do this first before I started the VBA approach but could not figure out how to do it. Any thoughts on this approach? Thanks again.

Cheers,
Big D
Big D is offline   Reply With Quote
Old 07-21-2019, 02:26 AM   #7
moke123
Me.Dirty=True
 
moke123's Avatar
 
Join Date: Jan 2013
Location: Massachusetts
Posts: 722
Thanks: 1
Thanked 221 Times in 207 Posts
moke123 will become famous soon enough
Re: Copy Last Entered Record with DMAX and DLOOKUP VBA

@Micron - Thanks for following up with what I was too tired to type out last night.

Quote:
Do you know if it's possible to select two or more names in the AssignedTo field and have this reflected in the reports? This would solve the problem of not being able to assign more than one person to a task.
yes it is possible but, whatever you do, avoid using a multi-valued field. They can be as much trouble as a lookup field in a table. Use a junction table which at the very least has a PK, Person FK, and Task FK. I would also add a date assigned and a date completed field depending on your requirements.

You may also want to re-think using attachment fields. They tend to bloat the size of a database rather quickly. Many developers only store the path to a file as text in a field which is simpler to deal with than attachment fields.

moke123 is offline   Reply With Quote
Old 07-21-2019, 06:46 AM   #8
Big D
Newly Registered User
 
Join Date: Jul 2019
Posts: 17
Thanks: 2
Thanked 0 Times in 0 Posts
Big D is on a distinguished road
Re: Copy Last Entered Record with DMAX and DLOOKUP VBA

Quote:
Originally Posted by moke123 View Post
@Micron - Thanks for following up with what I was too tired to type out last night.


yes it is possible but, whatever you do, avoid using a multi-valued field. They can be as much trouble as a lookup field in a table. Use a junction table which at the very least has a PK, Person FK, and Task FK. I would also add a date assigned and a date completed field depending on your requirements.

You may also want to re-think using attachment fields. They tend to bloat the size of a database rather quickly. Many developers only store the path to a file as text in a field which is simpler to deal with than attachment fields.
Micron, I am not familiar with junction tables having a PK, Person FK, and Task FK. What is that exactly and how will it solve my problem of being able to assign two or more people to a task? Do you have an example of this that you can share? Thanks very much...

Cheers,
Big D
Big D is offline   Reply With Quote
Old 07-21-2019, 07:01 AM   #9
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 794
Thanks: 10
Thanked 161 Times in 155 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Copy Last Entered Record with DMAX and DLOOKUP VBA

Quote:
Do you know if it's possible to select two or more names in the AssignedTo field and have this reflected in the reports? This would solve the problem of not being able to assign more than one person to a task.
With your combo, not that I know of because you've based the combo on a select query. While a query can show all mv data, I suspect it cannot provide a multi select field itself. I think only a table can do that. I waffle here because as mentioned, I have little or no experience with your design approach.

IMHO your only option would be to add a listbox to get multi select, but I don't see how that would help or how the form you mentioned relates to reports.
EDIT - I see our posts crossed. Junction tables are probably covered in at least 1 of the links I provided. If not, my friend Google will give you plenty to look at. I doubt very much that you've had the time to investigate what I provided. Suggest you put your current issue aside and check them out.
Micron is offline   Reply With Quote
Old 07-21-2019, 08:16 AM   #10
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,534
Thanks: 57
Thanked 2,419 Times in 2,319 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Copy Last Entered Record with DMAX and DLOOKUP VBA

do you still want to copy the latest record.
here it is.
Attached Files
File Type: zip WSC Shops Production Scheduling Database Rev1.zip (353.2 KB, 10 views)
__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
Old 07-21-2019, 10:15 AM   #11
Big D
Newly Registered User
 
Join Date: Jul 2019
Posts: 17
Thanks: 2
Thanked 0 Times in 0 Posts
Big D is on a distinguished road
Re: Copy Last Entered Record with DMAX and DLOOKUP VBA

Quote:
Originally Posted by arnelgp View Post
do you still want to copy the latest record.
here it is.
Thanks very much arnelgp; I good....

Cheers,
Big D
Big D is offline   Reply With Quote
Old 07-21-2019, 10:21 AM   #12
Big D
Newly Registered User
 
Join Date: Jul 2019
Posts: 17
Thanks: 2
Thanked 0 Times in 0 Posts
Big D is on a distinguished road
Re: Copy Last Entered Record with DMAX and DLOOKUP VBA

Anyone here know anything about developing a database with Office 365 PowerApps and SharePoint? I've seen some references to Microsoft no longer supporting Access Web Apps and suggestions to using Office 365 PowerApps and SharePoint but the literature currently out there is very high level lacking sufficiently detail.

Cheers,
Big D
Big D is offline   Reply With Quote
Old 07-22-2019, 12:17 PM   #13
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,772
Thanks: 13
Thanked 1,498 Times in 1,424 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Copy Last Entered Record with DMAX and DLOOKUP VBA

Correct. AWA are no longer supported and this is a very different question from where you started. Are you sure you need a web app? Are the people who are sharing the app all located on the same LAN or might they be in different locations? Have you considered using Citrix or Remote Desktop to support remote users? That will allow you to stick with Access. If you convert the BE to SQL Server and make some changes to the app, you might also be able to use a VPN for the remote users but I can't guarantee that this won't be slow. Citrix and RD will be more than fast enough. In fact, the remote users might get better response than the local users.

If you have no programming experience, you will be hard pressed to create any kind of meaningful web app. You could go with a hybrid if you have SharePoint and use the Access FE but use SharePoint lists as the BE. Normally I don't recommend this since the lists option can be very slow if the row count gets above a few thousand. But if you just want to publish "reports" from yesterday, exporting to SharePoint is quite viable.

We need to know more about what you need (vs want) to support to advise further.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
DLookup and DMax BEPGroup Forms 1 04-19-2013 03:57 AM
Dlookup with DMax on date poporacer Reports 1 12-15-2012 06:42 AM
Dlookup and Dmax Edgarr8 Forms 3 01-28-2011 07:01 AM
Possibility of using DLookup & DMax together Ashfaque Modules & VBA 7 01-17-2010 12:33 PM
DLOOKUP and DMAX Together Beerman3436 Modules & VBA 0 01-08-2001 08:49 AM




All times are GMT -8. The time now is 06:38 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World