Newbie question - Data Entry from Excel userform into Access Datab

[Webtechie]

Hello,

I'm new to using Microsoft Access as a backend. I've tried to look at some
books and spent some time surfing the net, but didn't quite understand.

I have a data entry userform. The users will be inputting about 300 - 400
entries a day.

1) They enter a first name
2) I do a search on all records with the same firstname
3) Populate a listbox
4) If the user doesn't see the person, then they add a new user (the system
will also catch the duplicates)

Question:
============================================
1) Do I create a disconnected recordset to search the firstname and
populating the listbox?

2) What about all the new records that they add? How do I get all the
records back into Access? After each record or at the end of their keying
data (end of the day)?
===========================================
Currently for another process, I am doing inserts for each new record.

For i = 0 To .lstFamily.ListCount - 1
If .lstFamily.Selected(i) = True Then

udata.iEmployeeID = .cboEmployee.Value
udata.iFamilyID = .lstFamily.List(i)

mySQL = "insert into tblEmployeeClub(EmployeeID, FamilyID) " & _
"values(" & udata.iEmployeeID & ", " & udata.iFamilyID & ");"

Set cmAccess = New ADODB.Command
myConnection.Open

With cmAccess
.ActiveConnection = myConnection
.CommandText = mySQL
.Execute
End With
myConnection.Close
Set cmAccess = Nothing
End If
Next

This inserts new records for each new entry the user makes into a listbox.
However, I'm wondering for the data entry form is the above the best way.

The data entry form has 6 fields (text boxes) that are being keyed.

I am using Excel 2007 and Access 2007.

Thanks

Tony

 

[Joel]

Below is code that adds items to the end of a database. To specific people
from the database use a WHERE statement in the SQL to filter the data you to
get into the listbox.

mySQL = "insert into tblEmployeeClub(EmployeeID, FamilyID) " & _
"values(" & udata.iEmployeeID & ", " & udata.iFamilyID & ")" & vbcrlf
& _
"where udata.EmployeeID = """ & "Joel" & """;"


A list box doesn't allow you to add information, but a combobox will let
you manually add entries. but it may be better to just have a different box
for the new info to be added.




Sub Submit()
'filename of database is with MakeDatabase macro

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

strDB = Folder & FName

If Dir(strDB) = "" Then
MsgBox ("Database Doesn't Exists, Create Database" & strDB)
MsgBox ("Exiting Macro")
Exit Sub
End If

ConnectStr = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Folder & FName & ";" & _
"Mode=Share Deny None;"

cn.Open (ConnectStr)
With rs
.Open Source:="Submissions", _
ActiveConnection:=cn, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
Options:=adCmdTable

If .EOF <> True Then
.MoveLast
End If
End With

With Sheets("Internal Project Plan")

ClientName = .Range("B4")
ImpMgr = .Range("B5")
LaunchDate = .Range("C4")

LastRow = .Range("K" & Rows.Count).End(xlUp).Row
For RowCount = 7 To LastRow

If UCase(.Range("K" & RowCount)) = "X" Then

DueDate = .Range("E" & RowCount)
ActualDate = .Range("F" & RowCount)
DateDif = .Range("M" & RowCount)
Accurate = .Range("L" & RowCount)
Task_ID = .Range("B" & RowCount)

With rs
.AddNew
!Task_ID = Task_ID
![Client Name] = ClientName
![Effective Date] = LaunchDate
![Imp Mgr] = ImpMgr
![Due Date] = DueDate
![Actual Date] = ActualDate
![Date Difference] = DateDif

.Update
End With
End If
Next RowCount

End With

Set appAccess = Nothing
End Sub

 

[Webtechie]

Joel,

Just to be clear.

1) It looks like you are creating the recordset each time this submit
routine is run.
2) For a data entry form, each time they hit the save button, should I
create a new recordset?

3) Or do I create a recordset when the form is opened? Then as they enter
data use a disconnected recordset (open it, addnew, update and then close it)?


Thanks for taking the time to help.

Tony

Below is code that adds items to the end of a database. To specific people
from the database use a WHERE statement in the SQL to filter the data you to
get into the listbox.

mySQL = "insert into tblEmployeeClub(EmployeeID, FamilyID) " & _
"values(" & udata.iEmployeeID & ", " & udata.iFamilyID & ")" & vbcrlf
& _
"where udata.EmployeeID = """ & "Joel" & """;"


A list box doesn't allow you to add information, but a combobox will let
you manually add entries. but it may be better to just have a different box
for the new info to be added.




Sub Submit()
'filename of database is with MakeDatabase macro

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

strDB = Folder & FName

If Dir(strDB) = "" Then
MsgBox ("Database Doesn't Exists, Create Database" & strDB)
MsgBox ("Exiting Macro")
Exit Sub
End If

ConnectStr = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Folder & FName & ";" & _
"Mode=Share Deny None;"

cn.Open (ConnectStr)
With rs
.Open Source:="Submissions", _
ActiveConnection:=cn, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
Options:=adCmdTable

If .EOF <> True Then
.MoveLast
End If
End With

With Sheets("Internal Project Plan")

ClientName = .Range("B4")
ImpMgr = .Range("B5")
LaunchDate = .Range("C4")

LastRow = .Range("K" & Rows.Count).End(xlUp).Row
For RowCount = 7 To LastRow

If UCase(.Range("K" & RowCount)) = "X" Then

DueDate = .Range("E" & RowCount)
ActualDate = .Range("F" & RowCount)
DateDif = .Range("M" & RowCount)
Accurate = .Range("L" & RowCount)
Task_ID = .Range("B" & RowCount)

With rs
.AddNew
!Task_ID = Task_ID
![Client Name] = ClientName
![Effective Date] = LaunchDate
![Imp Mgr] = ImpMgr
![Due Date] = DueDate
![Actual Date] = ActualDate
![Date Difference] = DateDif

.Update
End With
End If
Next RowCount

End With

Set appAccess = Nothing
End Sub

Hello,

I'm new to using Microsoft Access as a backend. I've tried to look at some
books and spent some time surfing the net, but didn't quite understand.

I have a data entry userform. The users will be inputting about 300 - 400
entries a day.

1) They enter a first name
2) I do a search on all records with the same firstname
3) Populate a listbox
4) If the user doesn't see the person, then they add a new user (the system
will also catch the duplicates)

Question:
============================================
1) Do I create a disconnected recordset to search the firstname and
populating the listbox?

2) What about all the new records that they add? How do I get all the
records back into Access? After each record or at the end of their keying
data (end of the day)?
===========================================
Currently for another process, I am doing inserts for each new record.

For i = 0 To .lstFamily.ListCount - 1
If .lstFamily.Selected(i) = True Then

udata.iEmployeeID = .cboEmployee.Value
udata.iFamilyID = .lstFamily.List(i)

mySQL = "insert into tblEmployeeClub(EmployeeID, FamilyID) " & _
"values(" & udata.iEmployeeID & ", " & udata.iFamilyID & ");"

Set cmAccess = New ADODB.Command
myConnection.Open

With cmAccess
.ActiveConnection = myConnection
.CommandText = mySQL
.Execute
End With
myConnection.Close
Set cmAccess = Nothing
End If
Next

This inserts new records for each new entry the user makes into a listbox.
However, I'm wondering for the data entry form is the above the best way.

The data entry form has 6 fields (text boxes) that are being keyed.

I am using Excel 2007 and Access 2007.

Thanks

Tony

 

[Joel]

You can do it either way. The recordset will filter you data automatically
when you use a WHERE in the SQL. So if you don't use the WHERE and then go
through the each returned entry to get the data you are looking for.

The recordset is just an array of links into the database for the enties you
are look for. The recordset will return only the columns of the database you
need. If the recordset returns an EOF then you move to the last entry in the
database and add a new item.

You don't acutally create a new recordset. You re-use the rs recordset just
filling it with new data. Actually not the data but links to the data.

Joel,

Just to be clear.

1) It looks like you are creating the recordset each time this submit
routine is run.
2) For a data entry form, each time they hit the save button, should I
create a new recordset?

3) Or do I create a recordset when the form is opened? Then as they enter
data use a disconnected recordset (open it, addnew, update and then close it)?


Thanks for taking the time to help.

Tony

Below is code that adds items to the end of a database. To specific people
from the database use a WHERE statement in the SQL to filter the data you to
get into the listbox.

mySQL = "insert into tblEmployeeClub(EmployeeID, FamilyID) " & _
"values(" & udata.iEmployeeID & ", " & udata.iFamilyID & ")" & vbcrlf
& _
"where udata.EmployeeID = """ & "Joel" & """;"


A list box doesn't allow you to add information, but a combobox will let
you manually add entries. but it may be better to just have a different box
for the new info to be added.




Sub Submit()
'filename of database is with MakeDatabase macro

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

strDB = Folder & FName

If Dir(strDB) = "" Then
MsgBox ("Database Doesn't Exists, Create Database" & strDB)
MsgBox ("Exiting Macro")
Exit Sub
End If

ConnectStr = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Folder & FName & ";" & _
"Mode=Share Deny None;"

cn.Open (ConnectStr)
With rs
.Open Source:="Submissions", _
ActiveConnection:=cn, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
Options:=adCmdTable

If .EOF <> True Then
.MoveLast
End If
End With

With Sheets("Internal Project Plan")

ClientName = .Range("B4")
ImpMgr = .Range("B5")
LaunchDate = .Range("C4")

LastRow = .Range("K" & Rows.Count).End(xlUp).Row
For RowCount = 7 To LastRow

If UCase(.Range("K" & RowCount)) = "X" Then

DueDate = .Range("E" & RowCount)
ActualDate = .Range("F" & RowCount)
DateDif = .Range("M" & RowCount)
Accurate = .Range("L" & RowCount)
Task_ID = .Range("B" & RowCount)

With rs
.AddNew
!Task_ID = Task_ID
![Client Name] = ClientName
![Effective Date] = LaunchDate
![Imp Mgr] = ImpMgr
![Due Date] = DueDate
![Actual Date] = ActualDate
![Date Difference] = DateDif

.Update
End With
End If
Next RowCount

End With

Set appAccess = Nothing
End Sub

Hello,

I'm new to using Microsoft Access as a backend. I've tried to look at some
books and spent some time surfing the net, but didn't quite understand.

I have a data entry userform. The users will be inputting about 300 - 400
entries a day.

1) They enter a first name
2) I do a search on all records with the same firstname
3) Populate a listbox
4) If the user doesn't see the person, then they add a new user (the system
will also catch the duplicates)

Question:
============================================
1) Do I create a disconnected recordset to search the firstname and
populating the listbox?

2) What about all the new records that they add? How do I get all the
records back into Access? After each record or at the end of their keying
data (end of the day)?
===========================================
Currently for another process, I am doing inserts for each new record.

For i = 0 To .lstFamily.ListCount - 1
If .lstFamily.Selected(i) = True Then

udata.iEmployeeID = .cboEmployee.Value
udata.iFamilyID = .lstFamily.List(i)

mySQL = "insert into tblEmployeeClub(EmployeeID, FamilyID) " & _
"values(" & udata.iEmployeeID & ", " & udata.iFamilyID & ");"

Set cmAccess = New ADODB.Command
myConnection.Open

With cmAccess
.ActiveConnection = myConnection
.CommandText = mySQL
.Execute
End With
myConnection.Close
Set cmAccess = Nothing
End If
Next

This inserts new records for each new entry the user makes into a listbox.
However, I'm wondering for the data entry form is the above the best way.

The data entry form has 6 fields (text boxes) that are being keyed.

I am using Excel 2007 and Access 2007.

Thanks

Tony