Solved

Access 2007 Database

Posted on 2013-11-21
4
267 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 300 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 36

Assisted Solution

by:PatHartman
PatHartman earned 200 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

749 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