Append More Than One, But Not All

[UserX]

The Form used is a daily log of customers. From this Form, I can select any ONE record, and then append that record into a separate Table/Form. What I would like to do is append some, but not all of the records. Let's see if I can't make this clearer...



Customers are signed in using an Access 2007 Form, called Customer Sign-in (clever, I know), which is backed by a table to hold the data. I made a separate Form, called Sign-in Log, which replicates the appearance of the Table that contains the customer info. From the Sign-in Log Form, I am currently able to select any ONE record, and then append it to a different Form/Table for further editing. This Form is called Workload.

When I attempt to select more than one record, I'm left with the impression that I am, in fact, not selecting more than one record based on these two observations:

1. In the Form view of Sign-in Log, the current record selected has an arrow at the leftmost position of the record line (despite being able to "highlight" more than one record).

2. Although I have created a second box for the Fields I would like to have displayed on the Workload Form (would like to add more, but as of now, only a second box set was created for testing purposes), only the record with the arrow (as opposed to all perceived selected due to the seeming highlight) is displayed; and displayed twice.

I imagine that I will need to further clarify this concern, but in the mean time, have a looksee at my sample db.

[pbaldy]

I'm not sure you can select multiple records that way and work with them. Does a multiselect listbox work for you?

http://www.baldyweb.com/MultiselectAppend.htm

[June7]

Why are you duplicating data in multiple tables? There is no info in the Workload table that isn't already in SignIn.

The triangle indicates the current active record. Yes, multiple records can be selected (must be continuous) and code can work with that selected set.

The same record displays twice because the two sets of controls are bound to the same fields. The form is set for Single Form view therefore only one record can be displayed and since both sets of controls are bound to the same fields, both sets show the same record data.

[UserX]

pbaldy,

I'm not sure if listboxes will help, but I'll take a look at the referrenced link. Thank you for your suggestion.

[UserX]

June7,

Why the dupes? Well, here's the breakdown of my db:

1. Customer comes into office, and then our front desk staff use the Customer Sign-in Form as a means of checking them in.
2. From the Customer Sign-in Form, the Sign-in Log can be refreshed showing the complete list of customers checked in for the day.
3. As a means of identifying what we helped our customers with, the record selected can be sent to another table for further input. This is what the Workload Form is for.
4. I can then build reports from the data collected.

Due to the large size of my functional db, I had to remove quite a bit in order to upload a sample for viewing. In it's grand condition, the db is upwards of ~41MB for the backend and ~22MB for the frontend.

Thank you for pointing out the Single Form view handicap. Can you provide insight on changing this to a view inwhich my goal is attainable?

Edit: After playing around with the Form Properties, I discovered the way to change the default view; however, this does not seem like my problem, though I will concede that I am not certain. From the looks of things, the problem seems to be not being able to select more than one record. From what I can tell, only Form View and List View allow me to have buttons that can peform the append query when clicked. If this is indeed the limitation of Access, what can I do to:

1. Select multiple records from the Sign-in Log Form; and
2. Append said multiple records

For what it's worth, I'm not stuck on the idea of an append query. If you can think of some other way to accomplish my goal, I'm willing to try it.

Thanks again for the expedious reply.

[June7]

I suggest the Workload table should just have the ID of the SignIn record and not repeat the individual's name nor the date. Then what other data will be in the Workload table? Will there be multiple workload records for each sign in?

Then possibly use form/subform arrangement for data entry. Main form bound to SignIn and subform bound to Workload.

Here is extract of code for how I work with multiple selected records:

Dim intHeight As Integer    'stores value for number of tests selected for deletion
Dim intTop As Integer   'stores value for position of the first selected record in Tests recordset
intHeight = Me.SelHeight
intTop = Me.SelTop
With Me.RecordsetClone
If .RecordCount < 1 Then
    MsgBox "No tests have been saved.  Delete action canceled.", , "RemoveTest Error"
    GoTo Exit_proc
ElseIf intHeight < 1 Then
    MsgBox "No tests have been selected.  Delete action canceled.", , "RemoveTest Error"
    GoTo Exit_proc
ElseIf MsgBox("This action may delete any saved test data.  Proceed?", vbExclamation + vbOKCancel, "Delete Test?") = vbCancel Then
    GoTo Exit_proc
End If
For N = 1 To intHeight
    .AbsolutePosition = intTop - 1    'AbsolutePosition property is 0 based counter so must -1 to get position within the recordset
    strTestNum = !TestNum
    .MoveFirst
    If intHeight > 1 Then
        intTop = intTop + 1
       'a bunch of code dealing with record
       .MoveNext
    End If
Next
End With

[UserX]

June7,

I have been trying, without success, to figure out how your suggestion would work, but alas, the idiot inside me just doesn't quite get it.

I've created the form/subform with the correct bindings; however, i'm still at my original problem: I can't figure out how to select more than one record at a time.

What I was able to accomplish, though, was to make my Workload Form able to show multiple records, instead of one, repeated.

I've adjusted my test db, now called test2. I've added a Form that allows addition to the Sign in Table. My goal is to be able to get an amount of my choosing into the Workload Form/Table, so that I can add the amount of application counts (indicated by the number 1) the customer was assisted with.

Please help. Thank you.

I suggest the Workload table should just have the ID of the SignIn record and not repeat the individual's name nor the date. Then what other data will be in the Workload table? Will there be multiple workload records for each sign in?

Then possibly use form/subform arrangement for data entry. Main form bound to SignIn and subform bound to Workload.

Here is extract of code for how I work with multiple selected records:

Dim intHeight As Integer    'stores value for number of tests selected for deletion
Dim intTop As Integer   'stores value for position of the first selected record in Tests recordset
intHeight = Me.SelHeight
intTop = Me.SelTop
With Me.RecordsetClone
If .RecordCount < 1 Then
    MsgBox "No tests have been saved.  Delete action canceled.", , "RemoveTest Error"
    GoTo Exit_proc
ElseIf intHeight < 1 Then
    MsgBox "No tests have been selected.  Delete action canceled.", , "RemoveTest Error"
    GoTo Exit_proc
ElseIf MsgBox("This action may delete any saved test data.  Proceed?", vbExclamation + vbOKCancel, "Delete Test?") = vbCancel Then
    GoTo Exit_proc
End If
For N = 1 To intHeight
    .AbsolutePosition = intTop - 1    'AbsolutePosition property is 0 based counter so must -1 to get position within the recordset
    strTestNum = !TestNum
    .MoveFirst
    If intHeight > 1 Then
        intTop = intTop + 1
       'a bunch of code dealing with record
       .MoveNext
    End If
Next
End With

[June7]

Select multiple records by holding down shift key then clicking the last desired record. As noted, must be continuous selection. Can't do like in a spreadsheet with control key for discontinuous selection.

I don't understand what you are trying to accomplish. Why not use a form/subform? If you want the login to appear as datasheet, consider: http://www.fmsinc.com/MicrosoftAcces...edSubforms.asp

The Workload table still duplicates data. Which field is for application counts?

Fields HouseGoods and Vehicles is not a normalized structure. Normalized would be a field for ItemType and a field for Quantity. How many different items will there be?


Advise to avoid spaces and special characters/punctuation (underscore is exception) in naming convention.

[UserX]

Ultimately, what I'm trying to accomplish is to track the inner workings of my office. Here we assist customers by creating applications to move their household goods and vehicles, respectively. So, using the test2 db, if you were to sign in a customer using the form Customer Sign-in (naming conventions have been taken under advisement), and then click on the button, you could then see the name you just entered appear on the Sign-in Log Form. Due to the size limit of attachments, I had to seriously remove a lot of functions that would otherwise make the db easier to function and understand.

The Sign-in Log Form acts as a list that the staff here can refer to when they need to create a workload. This can be accomplished by clicking the "Add to Workload" button. Upon clicking the button, a script is activated that transfers the select record (and here's where i'm hoping to move more than one at a time) to the workload table, and also opens the workload form. With the workload form opened, the staff can indicate the amount of applications of "House Goods" and/or "vehicle" were done for the customer.

Consider this example:

Kermit Frog is signed in by a staff member at the reception desk. Counselor "SK" helps Kermit with one each, a household goods and vehicle application. Counselor SK will refer to the Sign-in Log Form; find Kermit Frog; click on the record line containing Kermit Frog; click on the Add to workload button; and then finally type in the number 1 into the fields "House Goods" and "Vehicle". The reason that the workload table/form must contain the customer names is so I can create my reports at the end of the week/month, and these reports must contain the customer's info, as opposed to just the total amount of applications created.

In the grand scheme of things, the above explanation is how data entry/capture is done here in my office, and while it does work flawlessly, I'm seeking a method to improve the data entry/capture capability. The reason for this is because we also offer group counseling sessions, where we explain to a group of customers what they need to know for their move. To that end, we schedule these group counseling sessions to accomodate large groups of customers, so ideally what I'm trying to create here is a way to move more than one record at a time into the workload table/form, because doing one at a time for 75+ names an hour is pretty irritating.


I recognize that my explanations are rather cryptic, but it is because I'm not sure if I'm allowed to disclose the details of our work. I apologize.

Also, could you please clarify something: When I hold shift and then click X number of records from the top of the list, the selected records are highlighted, but still only the first (topmost, in this case) record is selected, but just in case I'm misunderstanding you...
I suspect that the reason I still can't move the multiple records is my codes, so I'll poke around.

Thanks again for your support.

[June7]

Can zip db for attaching to post - 2MB zip allowed.

Only one record can show the record selector pointer. However, a continuous group of records can be selected as a subset and code can work with that subset, as demonstrated in my posted code. Click on any record, hold shift key, click on another record (above or below) to select continuous group of records. The code will cycle through the subset and do something with each record. That something can be to write the data to another table.

Alternately, have a yes/no field in table and then check/uncheck bound checkbox on form. Then a query could pull data from only the checked records and write to another table. This will allow selection of non-continuous records.

[UserX]

Pbaldy,

After tinkering with you suggestions, I am beginning to see the potential it has. I ran into a bit of a snag, though. Using your code on the "Add records" button of your sample db, I changed the codes to reflect the tables in my db; however, only the number in the ID field copies over to the specified table. I.E. The primary key of record 5 will copy over to my workload table, but all that will copy is the number 5.

In the attached zip is my test db. I imported your tables and form so I could see how they work without having to switch back and forth between my test and your sample. If you have a spare moment, could you tell me what I'm doing wrong?

Ideally, what I'd like to do is to utilize that list box idea, and when the records are selected, have the names and rank of each record append to the workload table, so that when I open the workload form, I can see all of the records on the table, and then add the necessary data.

I'm not sure you can select multiple records that way and work with them. Does a multiselect listbox work for you?

http://www.baldyweb.com/MultiselectAppend.htm

[pbaldy]

The only field you've specified is LastName:

rs.AddNew
rs!LastName = ctl.ItemData(varItem)
rs.Update

If you want other fields populated, you need to add them, as I added one other in my sample:

rs.AddNew
rs!EmpID = ctl.ItemData(varItem)
rs!OtherValue = Me.txtOtherValue
rs.Update

[UserX]

Paul,

Using your example:

rs.AddNew
rs!EmpID = ctl.ItemData(varItem) <---- This line appends the data of only the bound column of your listbox.
rs!OtherValue = Me.txtOtherValue <---- This line appends the numeric data of your textbox.
rs.Update

The "rs!OtherValue" line may or may not be relevant to my problem at the moment. If i'm missing the big picture on the importance of this line of code and the box to which it refers, do let me know.

Duplicating the "rs!EmpID..." line, but changing "EmpID" to a different field still appends only the bound column data. So using your example still, if I were to add another line as you suggest and add another field (we'll call it "field1000") to your "tblOtherTable" for the sake of testing, the employee ID number would be appended into the EmpID field and field1000.

Instead, what I am trying to accomplish, with your code, is to have the entire row appended. So, using your lstEmployee listbox as the example... If I were to select "Baldy" from the list, and then click on Add Records, I would like to see the whole row of data (EmpID, EmpLName, EmpFName, etc) to the tblOtherTable.


Consider this example from my test db:

ID	LastName	FirstName	Rank	Date
1	Kidani	Shane	SPC	1/2/34
2	Simpson	Bart	Maj	1/2/34

What I would like to do is use your listbox idea to select multiple names from the Sign_in Log Form's "List60" listbox, and when clicking on button "Command59", the select rows of records (all data included: LastName, FirstName, Rank, & Date) append to the Workload table.

Thank you again for your time and patience.

[pbaldy]

Well, you'd replace "OtherValue" in:

rs!OtherValue

with the name of another field in the table that you want to populate. To refer to other columns of the listbox:

ctl.Column(x, varItem)

replacing x with the zero-based column number.

[UserX]

Paul,

Thank you. Your listbox idea is working just as I had hoped. I really appreciate your assistance.