Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 26th, 2005, 09:37 AM
Friend of Wrox
Join Date: Mar 2005
Location: , , .
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
Reply With Quote
  #2 (permalink)  
Old June 28th, 2005, 10:39 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Mar 2004
Location: Washington, DC, USA.
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


Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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

All times are GMT -4. The time now is 08:24 PM.

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