Using MS Access/forms/subforms
QUESTION: Hello Mr Scott, I have a data entry database (over the network) that works quite well-with the help of your tutorials-, I want to add a record to my backend table, so i can view how many forms each keyer(editor)entered daily.
Sencondly...in your creating login security using vba how do i get access to go to the main form after loading the 'frmMainMenu' form
Lastly, kindly point me to any link that help me in using access query.
Thanks for sharing your knowledge.
You don't do this by adding a record to a table. You need to have a CreatedBy and Createdwhen field in your table. The CreatedWhen field is populated by setting the Default value at the table level to =Now(). The CreatedBy field is populated by a hidden control on your data entry form with a default value of =Forms!frmLogin!cboUser.
You can then use the following query to get your counts:
SELECT CreatedBy, DateValue(CreatedWhen), Count(PrimaryKey) AS DailyEntries
GROUP BY CreatedBy, DateValue(CreatedWhen);
This will show a list of users and dates with the counts. If you want to show a list of dates just reverse the order of those fields.
frmMainMenu is your menu form that gives access to the rest of your app. There are many ways to do menu forms.
queries are just views of your data. what problems are you having using them?
---------- FOLLOW-UP ----------
QUESTION: Thanks for your response. How do i make a hidden control (i assume control means either a combobox or listbox).
I will greatly appreciate it if you tell where the codes should go. I can't find 'Default value' in my DB table property sheet. (we newbies dont actually know where to put the codes most times)
I have never tried making a query or report before. aside from using sort and find. I'd appreciate if you direct me on how/where to learn query and report.
thanks once again
ANSWER: Yes, you don't have fields on a form you have controls that may or may not be bound to a field in a table. This is a subtle but important distinction. Every control has a Visible property. By setting the property to No it will be hidden. The CreatedBy control I referred to should just be a textbox.
If you open a table in Design mode, select a Field and look in the Field Properties list below the fields list, you should see Default Value. By the way, I have no way of gauging what level of knowledge you have. So if I suggest anything you don't understand, try looking it up in the Help or do a search for it on the Web. For example you might have searched for Set Default Value for Access field
and found you answer. If you still can't find it feel free to ask for clarification. But by telling you to set "the Default value at the table level", I was telling you exactly where to put the code.
Have you tried using Query Design mode? If you select it from the ribbon, it prompts you to select a table (or tables). You can then drag and drop the columns you want to the grid. However, creating queries is a long topic that is beyond the scope of this medium, which is better suited to asking specific questions. I suggest you either get a book on Access. You can start, however with this site that has some excellent tutorials:
As for reports, try using the Report wizard.
---------- FOLLOW-UP ----------
QUESTION: Thanks so much Mr Scott. I have learn t so much from you already. My level of access is an intermediate one (so i think). I do more of data entry design using CSPro. I have one question (on form/subform) and i promise to let you rest after this, lol
I want to do a database where information from a certificate (containing cert info, child, father & mother information) are entered thru a form. my questions are
1. Do i create four tables(for cert,child,father & mother), use cert info as the main form & others as subforms. if yes, how do i link the tables to create a relationship inother to create the subforms
PS. I have achieved it by creating a single table (that contains all the infos) and a single form but i think its against the rules of normilization.
Kindly point me in the right direction and as always, your expert opinion/advice is appreciated.
I would see 2 tables, a table for certs and a child table for the people with a foreign key to the cert record and a field that identifies the family role. So it might look like this:
FamilyID (PK Autonumber)
FamMemberID (PK Autonumber)
FamMember (Father, Mother, Son, daughter, etc.)