Copy Last Entered Record with DMAX and DLOOKUP VBA (1 Viewer)

Big D

Registered User.
Local time
Today, 12:42
Joined
Jul 6, 2019
Messages
32
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
 

Attachments

  • SchedulingDB.zip
    894 KB · Views: 103

dynamictiger

Registered User.
Local time
Today, 19:42
Joined
Feb 3, 2002
Messages
270
What are you wanting to copy exactly the entry in which case grab the taskID or something else?
 

Big D

Registered User.
Local time
Today, 12:42
Joined
Jul 6, 2019
Messages
32
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
 

moke123

AWF VIP
Local time
Today, 15:42
Joined
Jan 11, 2013
Messages
3,852
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.
 

Micron

AWF VIP
Local time
Today, 15:42
Joined
Oct 20, 2018
Messages
3,476
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/2017/03/what-is-normalization-part-i.html
and/or http://holowczak.com/database-normalization/
Entity-Relationship Diagramming: Part I, II, III and IV http://rogersaccessblog.blogspot.ca/2009/01/entity-relationship-diagramming-part-i.html
How do I Create an Application in Microsoft Access? http://rogersaccessblog.blogspot.ca/2009/05/how-do-i-create-application-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/multivalued-fields/4594468763
 

Big D

Registered User.
Local time
Today, 12:42
Joined
Jul 6, 2019
Messages
32
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/2017/03/what-is-normalization-part-i.html
and/or http://holowczak.com/database-normalization/
Entity-Relationship Diagramming: Part I, II, III and IV http://rogersaccessblog.blogspot.ca/2009/01/entity-relationship-diagramming-part-i.html
How do I Create an Application in Microsoft Access? http://rogersaccessblog.blogspot.ca/2009/05/how-do-i-create-application-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/multivalued-fields/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
 

moke123

AWF VIP
Local time
Today, 15:42
Joined
Jan 11, 2013
Messages
3,852
@Micron - Thanks for following up with what I was too tired to type out last night.:)

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.
 

Big D

Registered User.
Local time
Today, 12:42
Joined
Jul 6, 2019
Messages
32
@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
 

Micron

AWF VIP
Local time
Today, 15:42
Joined
Oct 20, 2018
Messages
3,476
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.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:42
Joined
May 7, 2009
Messages
19,169
do you still want to copy the latest record.
here it is.
 

Attachments

  • WSC Shops Production Scheduling Database Rev1.zip
    353.2 KB · Views: 123

Big D

Registered User.
Local time
Today, 12:42
Joined
Jul 6, 2019
Messages
32
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:42
Joined
Feb 19, 2002
Messages
42,973
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.
 

Users who are viewing this thread

Top Bottom