Creating Multiple Field on an Event

[Emax]

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.

[puppydogbuddy]

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

[Jim Doherty]

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

On Error Resume Next
Me!lstMessages.RowSource = "Select 'Wait' As Entry,'Please Wait Processing your insertions...' AS Message"
Dim qryps As DAO.QueryDef, Curdb As Database
Dim strConn As String
Set Curdb = CurrentDb()
If Not IsNull(Curdb.QueryDefs("qryFetchInserts").SQL) Then    ' kills any prexisting query
  Curdb.QueryDefs.Delete "qryFetchInserts"
End If

Set qryps = Curdb.CreateQueryDef("qryFetchInserts")
 
strConn = "ODBC;DSN=yourservername;UID=your loginname;DATABASE=tester;Trusted_Connection=Yes"
qryps.Connect = strConn
qryps.SQL = "EXEC dbo.usp_DepEmployeeIns '" & Me!StartEmp & "','" _
& Me!EndEmp & "','" & Me!Dep & "','" & Me!Rc & "','" & Null & "'"
qryps.Close
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

CREATE PROCEDURE dbo.usp_DepEmployeeIns
(@StartEmp int,@EndEmp int,@Dep varchar(15)=null,@Rc varchar(15)=null,@strResult varchar(30) Output)
 AS
DECLARE @iterate int
DECLARE @mycheck int
DECLARE @message TABLE (Entry int IDENTITY(1,1), Message varchar(255))

SET NOCOUNT ON
IF @StartEmp is null OR @EndEmp is null
	BEGIN
	SELECT 'Invalid' As [Entry Attempt],'You must supply both a start employee number and an end employee number' as Message
	RETURN
END
IF @StartEmp ='' OR @EndEmp =''
	BEGIN
	SELECT 'Invalid' AS [Entry Attempt],'You must supply both a start employee number and an end employee number' as Message
	RETURN
END
IF @Dep is null OR @Rc is null
BEGIN
	SELECT 'Invalid' AS [Entry Attempt],'You must supply both a department  and an RC value' as Message
	RETURN
END
IF @StartEmp >@EndEmp
BEGIN
	SELECT 'Invalid' AS [Entry Attempt],'The start number cannot be greater than the end number' as Message
	RETURN
END

SET @iterate=0

WHILE @StartEmp<=@EndEmp
BEGIN
   IF NOT EXISTS(SELECT EmpID FROM DepEmployee WHERE EmpID=@StartEmp)  
   INSERT DepEmployee
   SELECT @StartEmp,@Dep,@Rc
   IF @@ROWCOUNT=0
	BEGIN
	PRINT 'Employees No '+ltrim(str(@StartEmp)) +' already entered in the database'
	INSERT @message
	SELECT  'Employee No '+ltrim(str(@StartEmp)) +' already entered in the database'
	END
   SELECT @StartEmp=@StartEmp+1
   SELECT @iterate=@iterate+1
	IF @StartEmp>@EndEmp
      	BREAK
   	ELSE
      	CONTINUE
	END

IF EXISTS(SELECT Top 1 Entry FROM @message)  
	BEGIN
	SELECT * FROM @message ORDER BY Entry
	END
	ELSE
	SELECT 'Success!!' As Entry,'All '+ ltrim(str(@iterate))+' inserts successful' as Message

Regards

Jim