Solved

Access 2007 Database

Posted on 2013-11-21
4
253 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
  • 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 35

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

825 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