By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,705 Members | 1,829 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,705 IT Pros & Developers. It's quick & easy.

Creating Multiple Field on an Event

P: 2
Problem:

I have created database in MS SQL and Data Form in MS Access 2003. The table DepEmployee has three column. EmpID [char(5)](Pk), Dep[varchar(15)], Rc[varchr(15)]
I want to auto generate EmpID with Dep, Rc when I click add button on Form.
I have two text boxes call Start EmpID and End EmpID.

For example

If I enter Start EmpID 50636 and End Emp ID 50736 database should store all EmpIDs between 50636 and 50736 automatically with other column data.

Please help to write above program.

Thanks for all.
Sep 3 '07 #1
Share this Question
Share on Google+
2 Replies


puppydogbuddy
Expert 100+
P: 1,923
Problem:

I have created database in MS SQL and Data Form in MS Access 2003. The table DepEmployee has three column. EmpID [char(5)](Pk), Dep[varchar(15)], Rc[varchr(15)]
I want to auto generate EmpID with Dep, Rc when I click add button on Form.
I have two text boxes call Start EmpID and End EmpID.

For example

If I enter Start EmpID 50636 and End Emp ID 50736 database should store all EmpIDs between 50636 and 50736 automatically with other column data.

Please help to write above program.

Thanks for all.

See method #2 for code sample:
http://support.microsoft.com/kb/210530
Sep 3 '07 #2

Jim Doherty
Expert 100+
P: 897
Problem:

I have created database in MS SQL and Data Form in MS Access 2003. The table DepEmployee has three column. EmpID [char(5)](Pk), Dep[varchar(15)], Rc[varchr(15)]
I want to auto generate EmpID with Dep, Rc when I click add button on Form.
I have two text boxes call Start EmpID and End EmpID.

For example

If I enter Start EmpID 50636 and End Emp ID 50736 database should store all EmpIDs between 50636 and 50736 automatically with other column data.

Please help to write above program.

Thanks for all.
Theres a bunch of ways for you to do this, simple ways or hard OTT ways you mentioned SQL server so I am going to give you one to chew over where data is 'accepted' on the 'Server side' using a stored procedure which you execute from the client side via a form. In this scenario the data is accepted or not by the procedure and reported back to you on success or failure by the server with little or no validation from the frontend Access form.

Forgive me but I don't know your skillset etc... so if none of this suits you I'm sure it will get the proverbial basket :)

In this example you use an unbound form and by simply giving a range of numbers for your EmpID (my assumption is they are numbers from my understanding of your question albeit I see your datatype has been given as varchar I am assuming you meant numeric given the between number to number intent of the question).

I see you have no Primary key defined so I am assuming you simply want to add a whole bunch of numbers into a table as quickly as you possible can where each of the employees entered have the same department and RC value at the time of input

This is only an example NOT the solution so I suggest you test it through to understand the logic of how you can... if you want to.. work both sides of the fence client side and server side...so to speak. I have set this procedure to basically look at all and any of the numbers entered when they reach the server and if they are ALREADY in the table to discount them and disallow entry.

I am assuming here that you MEANT to set a primary key on the EMPID field because my logical guesstimate is that you don't have an employee in more than one place (department) at a time...I may be wrong of course.

Now with the disallowing ..logically how would you know on the client side the server had rejected one or more numbers from those provided by the client?....we send back to the client a dataset of failures listing those records that are unacceptable. Conversely we report back if successful.

Now ordinarily we would interface with the client by messages in the form of VBA Msgboxes or SQL Server RAISERROR statements. You can still do all of that... but returning a table of errors cuts out clicking here there and everywhere. IN short the Server either accepts the LOT or it doesn't
accept those it considers to be duplicates from the range that you entered even though there is NO primary key set. (Which is the thing that should be doing the job in my book but anyway) I think you'll understand this is only a flavour of one of many methods I'm sure will be suggested to you in order to achieve your aim.

Hope it gives you one or two ideas

1) Create a table like this

CREATE TABLE [DepEmployee] (
[EmpID] [char] (10) NULL ,
[Dep] [varchar] (15) NULL ,
[Ec] [varchar] (15) NULL

2) Create a form with four unbound textboxes on and name them

StartEmp,EndEmp,Dep,Rc

3) Drop a command button on the form and call it cmdDothing
4) Drop a Listbox on the form and call it lstMessages
5) Set the column count property to 2
6) Set the column width property to 2cm;5cm

Go into the 'On click' property for the command button and paste the following code between the 'Private Sub Dothing_Click' line and 'End Sub' lines


Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next
  2. Me!lstMessages.RowSource = "Select 'Wait' As Entry,'Please Wait Processing your insertions...' AS Message"
  3. Dim qryps As DAO.QueryDef, Curdb As Database
  4. Dim strConn As String
  5. Set Curdb = CurrentDb()
  6. If Not IsNull(Curdb.QueryDefs("qryFetchInserts").SQL) Then    ' kills any prexisting query
  7.   Curdb.QueryDefs.Delete "qryFetchInserts"
  8. End If
  9.  
  10. Set qryps = Curdb.CreateQueryDef("qryFetchInserts")
  11.  
  12. strConn = "ODBC;DSN=yourservername;UID=your loginname;DATABASE=tester;Trusted_Connection=Yes"
  13. qryps.Connect = strConn
  14. qryps.SQL = "EXEC dbo.usp_DepEmployeeIns '" & Me!StartEmp & "','" _
  15. & Me!EndEmp & "','" & Me!Dep & "','" & Me!Rc & "','" & Null & "'"
  16. qryps.Close
  17. Me.lstMessages.RowSource = "qryFetchInserts"

The above is the command that will be activated by the click of the command button and it creates a query using an ODBC DSN connection that you can see take care to make sure that DSN string matches your machine ie your SQL server instance name and your windows login name as the authentication is integrated security in this example. If that is not your setup just research 'Connection strings' and ensure you get the right one for you and amend that line.

Now comes the SQL Server stored procedure. It is fairly lengthy and all of this you might think is "Ohhhh no so much to this for a simple piece of logic?"

It is not the most efficient... but I have done it so to thus demonstrate basic program flow and IF logic to you from an SQL server perspective in order that you see the distinct differences between VBA on the Access side and TRANSACT SQL language on the Server side.

Paste the following code into SQL Servers query analyser or whatever mechanism you are using to create your procedures

Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE dbo.usp_DepEmployeeIns
  2. (@StartEmp int,@EndEmp int,@Dep varchar(15)=null,@Rc varchar(15)=null,@strResult varchar(30) Output)
  3.  AS
  4. DECLARE @iterate int
  5. DECLARE @mycheck int
  6. DECLARE @message TABLE (Entry int IDENTITY(1,1), Message varchar(255))
  7.  
  8. SET NOCOUNT ON
  9. IF @StartEmp is null OR @EndEmp is null
  10.     BEGIN
  11.     SELECT 'Invalid' As [Entry Attempt],'You must supply both a start employee number and an end employee number' as Message
  12.     RETURN
  13. END
  14. IF @StartEmp ='' OR @EndEmp =''
  15.     BEGIN
  16.     SELECT 'Invalid' AS [Entry Attempt],'You must supply both a start employee number and an end employee number' as Message
  17.     RETURN
  18. END
  19. IF @Dep is null OR @Rc is null
  20. BEGIN
  21.     SELECT 'Invalid' AS [Entry Attempt],'You must supply both a department  and an RC value' as Message
  22.     RETURN
  23. END
  24. IF @StartEmp >@EndEmp
  25. BEGIN
  26.     SELECT 'Invalid' AS [Entry Attempt],'The start number cannot be greater than the end number' as Message
  27.     RETURN
  28. END
  29.  
  30. SET @iterate=0
  31.  
  32. WHILE @StartEmp<=@EndEmp
  33. BEGIN
  34.    IF NOT EXISTS(SELECT EmpID FROM DepEmployee WHERE EmpID=@StartEmp)  
  35.    INSERT DepEmployee
  36.    SELECT @StartEmp,@Dep,@Rc
  37.    IF @@ROWCOUNT=0
  38.     BEGIN
  39.     PRINT 'Employees No '+ltrim(str(@StartEmp)) +' already entered in the database'
  40.     INSERT @message
  41.     SELECT  'Employee No '+ltrim(str(@StartEmp)) +' already entered in the database'
  42.     END
  43.    SELECT @StartEmp=@StartEmp+1
  44.    SELECT @iterate=@iterate+1
  45.     IF @StartEmp>@EndEmp
  46.           BREAK
  47.        ELSE
  48.           CONTINUE
  49.     END
  50.  
  51. IF EXISTS(SELECT Top 1 Entry FROM @message)  
  52.     BEGIN
  53.     SELECT * FROM @message ORDER BY Entry
  54.     END
  55.     ELSE
  56.     SELECT 'Success!!' As Entry,'All '+ ltrim(str(@iterate))+' inserts successful' as Message

Regards

Jim
Sep 4 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.