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