Results 1 to 10 of 10
  1. #1
    jrmcgrath is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    6

    Post Do i need to use a macro, vba, or other(hammer)

    I am new to access and have been given a large task. I have been at it heavily for the past two months and have read the dummies book and have searched relentlessly on the web. So without further ado here's the trouble. I will state my problem or what I think my problem is and then I will tell you what I am trying to do so if there is a better way, I can go that route instead. I have several linked tables that I want to have a matching record created in the detail table every time a new record is created in the master table. the relationship is one to one. I have clicked on cascade delete which will delete the associated record when the master record is deleted.

    Here's the background. I am in the military and our company will be going from using Excel like a database to a real database. My first step was to create one large table that everyone can see, access and update regularly. The problem quickly came that we collect more than 255 (access' field limits per table) bits of information on everyone. The next step is more logical and I created several related tables. Here is where the first problem begins. A few of my tables are as followed. admin table (collects all basic information on people such as rank, date of rank, date of birth marital status etc., MT table that collects all of the data involved in what license they poses, expiration dates and when there explosive certification expires, Training Table that covered what badge qualification they have, schools and date completed etc. SCP table that covers everyone’s status of a security clearance, official passports, and a dozen dates that deal with that section. There is even a table that will house sensitive data like SSNs Passport numbers etc. For most of these tables I NEED a record created even if there is no data to input at that time so we can show the state of readiness. I have set the primary key to auto number on the master table and a long integer for the detail tables. B/c the name and rank is only stored in the master table I would like you view and search based on these fields so I can identify the person before adjusting the motor transport section. This is for the human factor as I know access can keep it straight based on the keys an relationship. I think the answer may be a macro but I haven’t found the answer. Thanks for whatever help comes. I can also post/ email the database or forms if it will help as there is no personal data in it.

  2. #2
    jrmcgrath is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    6
    I forgot to add that this will be used on computers utilizing access 07. the Marine Corps is slow to update.

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Wouldn't a Null suffice when no record is available? A relational db does not care too much for "empty" records. It is a waste of space and time. Have you looked at the Nz() function?

  4. #4
    jrmcgrath is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    6
    It would only be a short hot minute before the following information was added. but do to the lack of access skills i would like to have the record auto created and waiting on the sections to update it.
    Second myself and the rest of the senior leadership would like to type a persons name and then be able to view all their details such as height weight, expeirence levels, licensing, schools etc the comes from all tables. I am not worried about space as the recorded will eventually be populated.

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I'm still not clear as to why you need an empty record; especially now that you said it will be created with values in just a "short hot minute".

  6. #6
    jrmcgrath is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    6
    For example. i have the DB set up to have a new Marine Check in to the Admin Section. At this point a new record exist in the master tbl. Later on that day or the very next day when he completes the check-in process with Motor T and training those sections will begin updating the information on those tables. Our Primary key is abstract so unless the new record is already added in the detail tbl it will be difficult to add the new Marine to the Detail table unless you know the primary key number. not every section sees every frm. just the forms they need to update their section's information. A few people will have the ability to see the big picture of everything on everyone but for that to happen i need to have an equal number of records all corresponding in every table.

  7. #7
    jrmcgrath is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    6
    Back to the orignal question. Is there a way to automate the creating of a linked record into several detail tables in Access 2007?

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    The answer is yes but it would require the use of VBA. How many tables and how many fields in each record?

  9. #9
    jrmcgrath is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2012
    Posts
    6
    As of right know I have it so when a new Marine checks in , his first step on the first form is to type the Txt Primary key into all tbls. the following forms begin the data process for a couple of the tbls. the remaining tbls will be populated as the information becomes available or as the checking process is completed. I was hoping to be able to construct a button or something that when pushed or when a certain part of the form/forms reached it will save the primary key, which is a combo of the first four letters of the last name married with their last four of the ssn. eg mcgr9999 (not my real last 4), to all the other tables. I don't understand what all the other fields in those tbls have to do with it. Not intending on being evasive but the field are many and different qty for each tbl. i just want to auto save 1 field per table which is the primary key field. Also, on your earlier comment about why creating emtpy record has given to me think further on how to construct a better DB and that may not necessarily have a bunch of blank fields. I think this is something that i may pursue but needs further consideration. Thanks for pointing me to VBA.

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you post a copy (jpg or pdf or word) of you tables and relationships? I have acc2003 and can not open an accdb.
    There are few things in your post that I think should be reconsidered.
    ...when a new Marine checks in , his first step on the first form is to type the Txt Primary key into all tbls.
    That is prone to error, and would be discouraged by many people. Why not fill in his name, rank serial number or whatever? And propagate to other tables as the data is entered/becomes available. Whats the purpose of first 4 of name and last 4 of ssn? Access is quite capable of dealing with an autonumber PK. You can create other fields and values that have meaning to humans if you need them. Will the various Sections deal with Name or Name and Rank etc. Surely if they can identify the individual to do their input/edits whatever, then Access and any necessary vba coding can associate the proper primary key and related records.

    Just my $ 0.02

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need help with macro
    By zaza123 in forum Programming
    Replies: 0
    Last Post: 07-06-2011, 09:26 PM
  2. Macro help
    By SFC in forum Access
    Replies: 4
    Last Post: 03-09-2011, 09:05 PM
  3. Help With Macro's
    By GDubbs780 in forum Programming
    Replies: 1
    Last Post: 02-25-2011, 11:43 AM
  4. Replies: 0
    Last Post: 01-12-2011, 12:43 PM
  5. VBA Macro
    By DSTR3 in forum Access
    Replies: 1
    Last Post: 11-30-2010, 11:42 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums