W
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
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