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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
3 Pages V < 1 2 3  (Go to first unread post)
   Reply to this topicStart new topic
> Page Control Forms, Access 2016    
 
   
FrankRuperto
post Apr 22 2020, 02:34 PM
Post#41



Posts: 969
Joined: 21-September 14
From: Tampa, Florida USA


Here's a revised schema that includes the lookup tables for valid ContractingOfficers and ContractingSpecialists. Whether you include the ContractingOfficer and Quote fields in the task order table, or not, you can now use a combo box to select the Officer and Specialist(s) for each task order. this way you will only be storing their ID numbers in the tables instead of having to manually type their names, and avoid possible misspelled name variations of the same person. Also notice I changed the names of some of the tables.
This post has been edited by FrankRuperto: Apr 22 2020, 02:36 PM
Attached File(s)
Attached File  FederalContractsER2.PNG ( 50.61K )Number of downloads: 6
Attached File  FederalContracts__2_.zip ( 35.6K )Number of downloads: 3
 

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
mmchaley
post Apr 22 2020, 03:33 PM
Post#42



Posts: 58
Joined: 26-April 16



Thank you for the new Schema - That answers my next question.

It is always good to have someone else look at process and lingo. After 25+ years of doing this I end up blending things that should not be blended.

As i started separating the fields between Task Order and Quote, I developed a distinct set of fields for each around information coming in (Task Order) and information going out (Quote)

Mark
This post has been edited by mmchaley: Apr 22 2020, 03:35 PM
Go to the top of the page
 
FrankRuperto
post Apr 22 2020, 03:56 PM
Post#43



Posts: 969
Joined: 21-September 14
From: Tampa, Florida USA


You're welcome. If you want, attach the db or the relationship diagram when you're done reorganizing all the fields so we can take a look at it.

Best wishes for success on your project thumbup.gif

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
mmchaley
post Apr 22 2020, 04:20 PM
Post#44



Posts: 58
Joined: 26-April 16



One more question - Do I check anything other than Enforce Referential Integrity?


Database with new structure attached

Thanks
This post has been edited by mmchaley: Apr 22 2020, 04:38 PM
Attached File(s)
Attached File  QuoteLog_be.zip ( 36.33K )Number of downloads: 2
 
Go to the top of the page
 
FrankRuperto
post Apr 22 2020, 06:06 PM
Post#45



Posts: 969
Joined: 21-September 14
From: Tampa, Florida USA


QUOTE
Do I check anything other than Enforce Referential Integrity?

Nothing else, always check that box and don't touch anything else in that popup.

1. ClientID in TaskOrder table is just a number field that doesn't join with anything?.. Maybe it needs a lookup table?.. Im still confused about its meaning!

2. In that attachment field, it may not be an image, but for pdf documents?... You still want to avoid saving attachments inside the db, they make db size bloat really fast and slows down the app in general!
This post has been edited by FrankRuperto: Apr 22 2020, 06:24 PM
Attached File(s)
Attached File  NewFedSchema.PNG ( 150.71K )Number of downloads: 8
 

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
FrankRuperto
post Apr 22 2020, 07:50 PM
Post#46



Posts: 969
Joined: 21-September 14
From: Tampa, Florida USA


Hi Mark,

Another advice: In lookup tables, like the one's you have for ContractingOfficers and Specialists, I always put a "Discontinued" Yes/No field. I'm assuming you renamed it to "Inactive" which is fine. The purpose of this field is so that if the value (e.g. Person) is no longer available, you can hide that value from showing up in combo boxes drop down lists when users are adding or updating records. You don't want to physically remove inactive values from lookup tables because you may have other records that have that value in it and removing them would cause a missing reference, so setting "Inactive" field to Yes (TRUE) is just a logical delete designed to prevent users from selecting it while still displaying it in previously added records. In your app, the combo box drop list will display names, but what gets stored in the table is the ID number, not the name.

hth, Frank
This post has been edited by FrankRuperto: Apr 22 2020, 07:51 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
mmchaley
post Apr 22 2020, 08:43 PM
Post#47



Posts: 58
Joined: 26-April 16



Thanks you.

The ClientID is an orphan. Not sure how the hidden objects are in there, I have always seen them, so have just ignored them.
Yes, Inactive is my version of discontinued

The attachment is for emails, without attachments.

Thanks again for all your input. It is going to make things a lot easier and gets rid of a problem i had on a prior database that I could not figure out.

Mark
Go to the top of the page
 
FrankRuperto
post Apr 22 2020, 10:14 PM
Post#48



Posts: 969
Joined: 21-September 14
From: Tampa, Florida USA


QUOTE
The ClientID is an orphan

I recall you mentioning a Client is the same thing as a ContractingSpecialist, so yes there's no need to have that field in the TaskOrder table since you already have a related ContractSpecialist child table.

QUOTE
The attachment is for emails, without attachments.

I still discourage you from using attachment fields in Access. Reasons are:

1. Database size grows quickly.
2. You cant easily display more than one email if several are linked to a task order.
3. Real difficult to export if for example in the future you were to migrate to a SQL-Server backend.

The standard practice for including large binary objects in Access applications is to store them externally in Windows files and reference the path where they're stored in a text field.

NOTE: If the emails are stored in Desktop Office Outlook, there's no need to even store the emails internally or externally as Access can directly integrate with them and you can even do neat things like automatically or manually send email replies, generate new templated emails which you can modify before sending, update contacts, etc. from within Access via Office Automation:
https://docs.microsoft.com/en-us/office/vba...sic-application

Access can also integrate with and automate other Office components such as Word, Excel, MS Project, etc.

hth, Frank
This post has been edited by FrankRuperto: Apr 22 2020, 10:29 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
mmchaley
post Apr 23 2020, 03:12 PM
Post#49



Posts: 58
Joined: 26-April 16



Frank - I think that helps. We just went to Office 365 for our email. It will also streamline a process I am trying to get stopped, that is saving emails everywhere.

For my Contracting Officer and Specialists, I want to put the drop down on Task Order Form (the task order Form is a subform in a MasterContracts Form). If I am remembering correctly, I create an unbound drop down control, but I can't remember how I save those values to the TaskOrderContractOfficer and TaskOrderContractSpecialist.

Can you point me in the right direction, some web search terms would even be helpful.

Thanks,
Mark

EDIT - I think I figured it out
This post has been edited by mmchaley: Apr 23 2020, 04:02 PM
Go to the top of the page
 
FrankRuperto
post Apr 23 2020, 03:48 PM
Post#50



Posts: 969
Joined: 21-September 14
From: Tampa, Florida USA


Hi Mark,

Here's the combobox tutorial:

https://www.techonthenet.com/access/combobo...d_index2013.php

Cheers cheers.gif

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
mmchaley
post Apr 23 2020, 04:18 PM
Post#51



Posts: 58
Joined: 26-April 16



I just want to make sure for the Contract Specialist, I would set the control source to

CODE
=[tbl_TaskOrderContractSpecialist]![ContractSpecialistID]


I have my row source as
SQL
SELECT tlkp_ContractSpecialist.ContractSpecialistID, [tlkp_ContractSpecialist]![FirstName] & " " & [tlkp_ContractSpecialist]![LastName] AS FullName
FROM tlkp_ContractSpecialist ORDER BY [tlkp_ContractSpecialist]![FirstName] & " " & [tlkp_ContractSpecialist]![LastName];
Go to the top of the page
 
FrankRuperto
post Apr 23 2020, 05:45 PM
Post#52



Posts: 969
Joined: 21-September 14
From: Tampa, Florida USA


Is that what the combo box wizard put in control source, or you didn't use the wizard? If your cbo is unbound then control source would be blank. If its bound then you can just put the field name, in your case, ContractSpecialistID.

Example:


Attached File(s)
Attached File  cboBound.PNG ( 131.76K )Number of downloads: 3
 

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
mmchaley
post Apr 24 2020, 10:34 AM
Post#53



Posts: 58
Joined: 26-April 16



I didn't use the wizard.

What I ended up doing (you will likely cringe) is dragging the tbl_TaskOrderContractingOfficer and Specialist tables onto frs_TaskOrderSubform then cleaned it up. What i tried in my last post ended up not working.
Go to the top of the page
 
FrankRuperto
post Apr 24 2020, 11:05 AM
Post#54



Posts: 969
Joined: 21-September 14
From: Tampa, Florida USA


QUOTE
...dragging the tbl_TaskOrderContractingOfficer and Specialist tables onto frs_TaskOrderSubform then cleaned it up.

Not sure I understand, did you mean you moved the cbo form fields to the order subform and kept the child tables as is, or did you add one officer and five specialist fields to the order table so you can put those fields on the order subform?

If there's always going to be only one officer per order, then you can add that field to the order table, but I never like to use the words "always" and "never" when it comes to db table designs. You can leave the officer and specialists detail tables as is and place their cbo's in the order subform. If you follow the instructions in the tutorial link I previously posted you shouldn't have any problems properly setting up the cbo's.

Are you aware you can also add new values, on_the_fly, to cbo form fields without having to close the form and manually add them to the lookup tables?
This post has been edited by FrankRuperto: Apr 24 2020, 11:07 AM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
mmchaley
post Apr 27 2020, 10:00 AM
Post#55



Posts: 58
Joined: 26-April 16



I just dropped the "link" table onto my form removed the taskID field and set the contratofficerID as my drop down lookup.

I did not know i could add them on the fly. I think I will forgo that for now as the contracting officers are fairly stable and the contracting specialists only change every 1 to 2 years

Thanks,
Mark
Go to the top of the page
 
FrankRuperto
post Apr 27 2020, 10:20 AM
Post#56



Posts: 969
Joined: 21-September 14
From: Tampa, Florida USA


Ok, so that means there's only one ContractingOfficer per TaskOrder, and there can be 1 to 5 Specialists, so those have to remain in a separate subform. Can one specialist simultaneously be assigned to multiple TaskOrders?

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
3 Pages V < 1 2 3


Custom Search


RSSSearch   Top   Lo-Fi    27th May 2020 - 08:16 PM