Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access 2007 Database

Posted on 2013-11-21
4
Medium Priority
?
291 Views
Last Modified: 2013-12-02
Database consist of a form with a couple of tabbed subforms.  The main form has the primary key and date which is displayed and entered at the very top of the form.  Is there a way to not allow the user to input anything in the subform/body until the primary key field has been autopopulated.   This was prompted due to testing...where the entire form was populated with data, but the PK field never populated and resulted in a unbound record.  Thanks in advance for your help.
0
Comment
Question by:EASCOA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39665984
Are you using an auto number or a custom PK field?
0
 

Author Comment

by:EASCOA
ID: 39666147
I guess it might be a custom PK--the department to identify their records...the primary key is set to start with "ST "000000000
0
 
LVL 61

Accepted Solution

by:
mbizup earned 1200 total points
ID: 39666203
Those custom numbers are usually triggerd by data entry in some required fields... so there are a couple of different approaches.

1.  Ensure that whatever field triggers the creation of your custom number has data in it before allowing the user to enter data in the subform.

In the Enter Event of the subform (main forms design):
if me.Requiredfield & "" = "" then 
msgbox "enter required data"
me.Requiredfield.setfocus
end if

Open in new window


OR

2.  Force the procedure that creates your custom number to run if the PK is blank/null:

if me.PK & "" = "" then 
   Call NameOfProcedureThatCreatesPK
   Me.Refresh
end if

Open in new window


Thats the general idea... but I can't get more specific without seeing how your database actually works.
0
 
LVL 39

Assisted Solution

by:PatHartman
PatHartman earned 800 total points
ID: 39669644
If you are generating the ID rather than using an autonumber, code in the mainform's BeforeUpdate event should validate its presence.

In the subform, use the on Dirty event.   Check to see if the ID control on the main form is populated.  Doesn't matter if it is an autonumber or user-generated.  If it is not present, cancel the event, undo the update, and move focus back to the main form.

If IsNull(Me.Parent!yourID) Then
    Cancel = True
    Me.Undo
    Me.Parent!somecontrol.SetFocus
    Msgbox "some message", vbOKOnly
    Exit Sub
End If 

Open in new window

0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question