Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old June 26th, 2005, 09:37 AM
Friend of Wrox
Join Date: Mar 2005
Posts: 264
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to add constraint to access bounded form.

How to add constraint to access bounded form.

Hi everybody .I created a bounded form that allows user to enter the following fields information
to the sql server db that is linked to access .The fields are: Projectno, taskcode, taskdiscription
and total hours worked.

I want for example when a user tries to enter total hours work for a user (Hours in hours worked table) and
if he did not already enter wages of the employee for that particular project (amount in hourly wages table)
that user get a massage prompt and ask user to enter amount in to hourly wages table first. Possibly opening that form.

I be happy if some one help me add this feature to my form. Thanks

   http://i5.photobucket.com/albums/y18...constraint.jpg =====> table reletionships pic
Old June 28th, 2005, 10:39 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

Well, without the structure, and with a few minutes to spare, here are the steps:

For the on click event of the submit button, collect the worker's ID number from your form, something like this:


Dim intWorker As Integer

intWorker = Me.WorkerID 'assumes there is a field with this name


Then call your SQL server, either using an installed DSN, or some other method, something like:

Dim strUserName As String
Dim strPassword As String
Dim strSQL As String

strUserName = "YourUserName"
strPassword = "YourPassword"
strSQL = "SELECT * FROM tblYourTable WHERE [WorkerID] = " & intWorker

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "DSN=YourDSN;", strUserName, strPassword
objRecordset.CursorLocation = adUseClient

objRecordset.Open strSQL, objConnection, adOpenStatic, adLockOptimistic


Then check the field containing the Wage value something like:


  If IsNull objRecordset.Fields(4) Then 'assumes wage is the fifth field

   intWage = 0
   intWage = objRecordset.Fields(4)
  End If


Then send a message based on this value.

If it is 0, then cancel the update, open the proper form to enter a wage, filtered for the Worker in question, and cancel the update.

Then on the on click event of the wage form, refresh the first form, and allow them to try the update again.

That was easy. :D



Similar Threads
Thread Thread Starter Forum Replies Last Post
How can i add constraint on update cascade without mahen_pali SQL Server 2005 1 May 3rd, 2008 03:28 PM
Cant ADD CONSTRAINT (ergent) melvik MySQL 3 April 18th, 2006 01:16 AM
How to design a query for this bounded form method Access 0 June 24th, 2005 05:47 AM
Problem with bounded form query method Access 0 June 23rd, 2005 12:01 PM
Access SQL alter table add constraint Skipmeok SQL Language 0 April 27th, 2004 08:02 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.