[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Choosing records from a form to send to a report

Posted on 2007-10-10
53
Medium Priority
?
375 Views
Last Modified: 2016-08-29
Hello:

I have a form that lists records of documents (drawings). The user can add and edit records on this form but that I would like the user to be able to also select records from the list on the form then send those chosen records to a report (transmittal). Perhaps there would be two controls , one to choose records for the report and another to open the report showing the chosen records to be tranmitted.  Is this possible? Thanks

LDP
0
Comment
Question by:LDP-bb
  • 29
  • 19
  • +1
50 Comments
 
LVL 15

Expert Comment

by:Simon Ball
ID: 20054984
yes.

Sounds like you need a list control for the user to select the records...an event to fire which will put those records into a made table.. and then a report which looks at the made table as its data source
0
 

Author Comment

by:LDP-bb
ID: 20057152
Thanks Sudonim:

Is there no way around duplicating the data in another table?
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 20057224
well yeah, you could have a few options i suppose...youcould make a table of just the primary keys from the list box, and have the report work of a query which uses the main datatable, plus the littl "index" table to define the records...
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:LDP-bb
ID: 20081257
I have a transmittal table (DTTable) with a primary DTNum field. I would like to add the DTNum field to the table of documents. Is it possible to use a multi-select list box on the document form, then set the DTNum in the document form them pass this to the Transmittal report using the DTnum set and records chosen on the document form but also append a listing of the chosen documents to a separate DTTable? I n case I would liek to re-produce this particular transmittal at alater date?
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 20083690
OK.

firstly then you want to have a multiselect list box and a function which records its selected items in two place....one will be a date stamped archive which you persists, and the other will be a temporary area which the transmittal report will link to in order to display its records....presumably then you will also need a combo box of report dates, and a function to append the relevant records from the archive back into the temporary table for when you want to reproduce a previous days reports?

I am not sure what you meant by this part:

Is it possible to use a multi-select list box on the document form, then set the DTNum in the document form them pass this to the Transmittal report using the DTnum set and records chosen

i get this bit:
Is it possible to use a multi-select list box on the document form

i don't know what this means:
 then set the DTNum in the document

then i am confused:
them pass this to the Transmittal report using the DTnum set and records chosen

if DTNUM is the primary key - how does it connect to the "records chosen"

i am sure what you want to do is possible, we can display records in a mutli-select list box, copy selected recordsinto an archive table, append records into a temporary table which the report can look at, and write something to reproduce previous reports...but i think we need more detail of what is required.

If we want to get more complicated, we could just have the archive table with a date stamp, and use that in the transmittal report, with the date as criteria?
0
 

Author Comment

by:LDP-bb
ID: 20233727
Sudonim:
I apologize for the delay. You are correct. "i am sure what you want to do is possible, we can display records in a mutli-select list box, copy selected recordsinto an archive table, append records into a temporary table which the report can look at, and write something to reproduce previous reports...but i think we need more detail of what is required."

That is pretty well it. But how now to proceed? How do i send the records from the multi-select box to an archive table? I have a transmittal table which is a table of document records assigned a transmittal number (DTNO) as a primary key. Can I run an append query with the data chosen from the multi=-select box or should the data chosen be sent to temporary table first? I have a field for the latest transmittal number for each record. Can i post a jpg of my form?
 
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 20239907
as far as i amaware there is no way to append the "copy" of the selected records from a multiselect list box control(MSLC) using an append query.  it has to be done using code, which is simple enough:

a button next tot he MSLC , which has the code in its onlck event,  needs to scroll through the MSLC checking if each item is selected...if it is, vb code runs an append for that item to add it to the temporary table....

once all the items are tested and appended, the whole content of the temporary table is appended with a date stamp to the archive table.. and then open the report.

i can help with code for all of that. i'll post something simple up for you in a bit
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 20240081
definitions:

Table - DTTable
fields:
id, autonumber
DTNUM, Text
Data, text
desc: contains dtnum and data
e.g.:
id      DTNUM      data
1      DT001      Test 1
2      DT002      Test 2
3      DT003      Test 3
4      DT004      Test 4
5      DT005      Test 5
6      DT006      Test 6

Table - tmptable
fields:
DTNUM text
desc: contains a temporay list of selected records for the report, from the mutliselect box
e.g.
DTNUM
DT001
DT003
DT005

table: Dtnum_report_archive
Fields:
ID - autonumber (primary key)
DTNUM Text
Datestamp Datetime

desc - holds the archived copies of the tmp_table contents
e.g.
id      DTNUM      Datestamp
1      DT001      08/11/2007 09:54:06
2      DT003      08/11/2007 09:54:06
3      DT005      08/11/2007 09:54:06

Form: form1
contains
Listbox called list0
datasource: SELECT DTTable.id, DTTable.DTNUM, DTTable.data FROM DTTable;
properties, other, multiselect = simple

command button command 2
on click event =

Private Sub Command2_Click()

Dim oItem As Variant
Dim sTemp As String

If Me!List0.ItemsSelected.Count <> 0 Then
' hide the append notification
DoCmd.SetWarnings false
'empty the temporary table
DoCmd.RunSQL "DELETE Temp_tbl.* FROM Temp_tbl;"

    For Each oItem In Me!List0.ItemsSelected
       
        ' used msg box to test th Dt00x values
        'MsgBox List0.Column(1, Me!List0.ItemData(oItem) - 1)

        ' run the code to insert the selected row
        DoCmd.RunSQL "insert into Temp_tbl (DTNUM) values ('" & List0.Column(1, Me!List0.ItemData(oItem) - 1) & "');"
       
       
       
    Next oItem
    ' run the append from tmptable to archive
    DoCmd.OpenQuery "DTNUM_append_tmp_to_arch"
    're-enable messages
    DoCmd.SetWarnings True
   
    'open report
    DoCmd.OpenReport "Dtnum_report_archive", acViewPreview, , , acWindowNormal
Else
    MsgBox "Nothing was selected from the list", vbInformation
    Exit Sub  'Nothing was selected
End If

End Sub

queries:

DTNUM_append_tmp_to_arch =
INSERT INTO Dtnum_report_archive ( DTNUM, Datestamp )
SELECT Temp_tbl.DTNUM, Now() AS Expr1
FROM Temp_tbl;

dtnum_tmp_report_data =
SELECT DTTable.data
FROM DTTable INNER JOIN Temp_tbl ON DTTable.DTNUM = Temp_tbl.DTNUM;

report:
Dtnum_report
created using wizard from query dtnum_tmp_report_data - shows the row from the dtnum table where th dtnum id is also present in the tmp_table




0
 

Author Comment

by:LDP-bb
ID: 20241520
sudonim:

I'll will go through this and try applying it to my forms, tables and reports form these particular records. I'm hoping the "archive table" can be my existing transmittal table that lists record data of documents sent with each transmittal to date, that would be great.  I really appreciate your effort in this and I'm confident this will work. I'll let you know when I have my trial in place. It would be so much easier if I could show you the forms/tables and reports I'm working with  but again, thanks for this sudonim, I'll get back to you.
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 20241542
there is a way for you to upload a copy of your mdb in a zip file onto experts exchange, so that if you get stuck, i can help out with implementing the above solution.

i don't know how to do it as i tend to answer more than ask in the mdb area.
0
 

Author Comment

by:LDP-bb
ID: 20406907
Sudonium:

I'm going to load a sample db with tables/forms and reports. TCheck www.ee-stuff.com  under 20241542.
This is bacially the sequence the user would follow. The user first opens the Drawing Transmittal Main Form, creates a new Transmittal record and fills in the information such as Date, Subject, Received /By, ToName etc. The user then opens the drawing list form that lists drawings he could add to the transmittal.

In the Transmittal Pick window, on the drawing list form the user selects the drawings he would like to add to the transmittal, when finished, the user clicks the ADD Drawings to Open Transmittal tab and the selected drawings get added to Transmittal Drawing List table with the DTNumber of the current record in the open Form (Drawing Transmittal Main Form) assigned to the chosen drawings.

The user then returns to Transmittal main form ands sees the chosen drawings added to the Document List Sub-form. The user then clicks the Preview Transmittal tab on the bottom of the form to print the transmittal with all record info listed including drawings assinged the DTnumber. I hope this is ok.  You could try a trial with Transmittal #4.

Thanks ldp
0
 

Author Comment

by:LDP-bb
ID: 20406974
Sudonium:

Whoops I'm not sure of the ability to upload the sample bd at www.ee-stuff.com. I'll have to look into this.

ldp
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 20409874
sounds good to me, which bits are you stuck on?
0
 

Author Comment

by:LDP-bb
ID: 20411777
sudonim

I've uploaded a partial database that holds the tables/forms and report I'm trying to wiork with. On ee-stuff go to question 2285273.
I've added a multiselect list box where the user will choose drawing numbers they would like to include in a transmittal. I've also added a empty control I was going to try to use to send the selections or append them to the Transmittal Drawing Lists and also have them appear in the open Drawing Transmittal Form. The user first opens the Drawing Transmittal form, establishes a new Transmittal number, DTNumber, enters Tdate, Subject, ReceivedBy, ToName etc. then opens the Drawing list forms (sample Archdwg) selects the drawings to include and appends the slected records to the tranmsittal drawing list and the Transmittal form they first opened. also the DTnmuber established on the transmittal form would be assigned to the Drawing in the Drawing list form (archdwg, see far right field DTnumber), eventually the drawing list would also show the last DTnumber each record was released on. I hope this is not too much to ask. Primarily it is appending the selcted records from the archdwg form to the Transmittal form and Transmittal Drawing List using the DTnumber as primary key.

Thanksk sudonim

ldp
0
 

Author Comment

by:LDP-bb
ID: 20583575
Any thoughts or news Sudonim? It's been a month. Thanks.
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 20607761
sorry - i have been off work for xmas and on paternity leave either side, not been near a pc.  oncei catch up on my work i'll certinaly do my best to help.
0
 

Author Comment

by:LDP-bb
ID: 20609316
Sudonim:

Let me know if there anyway I could help clarify my problem. Should I re-open the question if you don't have time?

ldp
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 20673477
sorry for the delay.

based on what info i have given and what you have done so far,  can you explain which points exactly you are stuck on.
0
 

Author Comment

by:LDP-bb
ID: 20675041
Sudonim:

I've uploaded a file (with screen shots of the database) that explains what I am having trouble with.
https://filedb.experts-exchange.com/incoming/ee-stuff/6632-Hello-Sudanim.pdf 
I have trouble taking the chosen records from the multi select list on one form and moving the chosen data to another table and open form.

Thanks
LDP
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 20688789
i'thought i had the db, but my pc's been rebuilt.  and i cannot find it on ie stuff...

looking at the pdf.

it seems like the button "add to open transmittal" neds to process selected items in the multiselect list box "transmittal pick" and write the name into the relevant field of the table, along with the currently selected record id, in this case DT4.

it seems that the user select the drawings for the new transmittal before the transmittal has been created - so at the point of clicking "add to transmittal" there is not yet an ID for the new transmittal.  if this is the case, then the system  will have to append the chosen drawing id's to a temporary table, and once the Transmittal ID is selected, then the two data sets can be merged.

it will be easier to rearrange the workflow of the app;lication to have the drawing selection routine occuring after the new transmittal has been created..

otherwise you will need to store the selected pics in a temp_tbl first like this:



this code processes themutliselect records into a temporary table, called temp_tbl,
Private Sub Command2_Click()
 
Dim oItem As Variant
Dim sTemp As String
 
If Me!List0.ItemsSelected.Count <> 0 Then
' hide the append notification
DoCmd.SetWarnings False
'empty the temporary table
DoCmd.RunSQL "DELETE Temp_tbl.* FROM Temp_tbl;"
 
    For Each oItem In Me!List0.ItemsSelected
        
    ' run the code to insert the selected row
    DoCmd.RunSQL "insert into Temp_tbl (DTNUM) values ('" & List0.Column(1, Me!List0.ItemData(oItem) - 1) & "');"
        
    Next oItem
    're-enable messages
    DoCmd.SetWarnings True
    
    'open report
    'DoCmd.OpenReport "Dtnum_report_archive", acViewPreview, , , acWindowNormal
Else
    MsgBox "Nothing was selected from the list", vbInformation
    Exit Sub  'Nothing was selected
End If
 
End Sub

Open in new window

0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 20688800
then on the form where the transmittal id is created,
you can lift the values from temp_tbl and append them into the transmittal drawing list.

having re-read your pdf it seems you are already creating the transmittal data first, is that form still open?

if so, the append sql above:
DoCmd.RunSQL "insert into Temp_tbl (DTNUM) values ('" & List0.Column(1, Me!List0.ItemData(oItem) - 1) & "');"

could be changed to be:

DoCmd.RunSQL "insert into [transmittal drawing list] (DTNUMber, docno) values ('" & 
[forms]![drawing transmittal main form]![transmittal DT Number].value & "','" & List0.Column(1, Me!List0.ItemData(oItem) - 1) & "');"

which would append each selected doc number into the table.
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 20688805
please check the form and field names, i only have the pdf so i have sed the forms and fields captions, not their actual object names..
Private Sub Command2_Click()
 
Dim oItem As Variant
Dim sTemp As String
 
If Me!List0.ItemsSelected.Count <> 0 Then
' hide the append notification
DoCmd.SetWarnings False
'empty the temporary table
DoCmd.RunSQL "DELETE Temp_tbl.* FROM Temp_tbl;"
 
    For Each oItem In Me!List0.ItemsSelected
        
    ' run the code to insert the selected row
    DoCmd.RunSQL "insert into [transmittal drawing list] (DTNUMber, docno) values ('" & 
[forms]![drawing transmittal main form]![transmittal DT Number].value & "','" & List0.Column(1, Me!List0.ItemData(oItem) - 1) & "');"
    
    Next oItem
    're-enable messages
    DoCmd.SetWarnings True
    
    acWindowNormal
Else
    MsgBox "Nothing was selected from the list", vbInformation
    Exit Sub  'Nothing was selected
End If
 
End Sub

Open in new window

0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 20688809
so if you re-post the db i'll put the solution code in the right place and test it for you ( might be next week now, i am on a course all day today) and then i can upload it back for you.
0
 

Author Comment

by:LDP-bb
ID: 20690158
Sudonim:

I will try to work with your code, it looks perfect. Your right, the user has the transmittal form open first, creates the DTNumber with it's metadata, then goes back opens the Drawing form and selects the drawings to go to the open transmittal form. I uploaded a sample of the database that you can test with.
you can get direct access to the file here...

 https://filedb.experts-exchange.com/incoming/ee-stuff/6654-LDP-Sample.zip or here

https://filedb.experts-exchange.com/incoming/ee-stuff/6632-Hello-Sudanim.pdfhttps://filedb.experts-exchange.com/incoming/ee-stuff/6654-LDP-Sample.zip




Thanks very much Sudonim

ldp
0
 

Author Comment

by:LDP-bb
ID: 20780298
Sudonim:

I tried your code and substituted the correct references to List numbers etc. but the code does not work. Did you have a chance to test it with the files I uploaded for you on the 18th? I understand you place the code on the OnClick event on the "Send to Transmittal" Control?

LDP
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 20786184
I have got it appending:


but i am confused - why does the Transmittal Drawing List table replicate fields from the source table ArchDwgList, plus the "approved field"

TDL table should just have its id, the dtnum, the doc num and the approved field?

surely the display on the form Drawing Transmittal Main Form should a query linking back to the ArchDwgList table?

Private Sub Command27_Click()
Dim oItem As Variant
Dim sTemp As String
If Me!List25.ItemsSelected.Count <> 0 Then
' hide the append notification
'DoCmd.SetWarnings False
'empty the temporary table
'DoCmd.RunSQL "DELETE Temp_tbl.* FROM Temp_tbl;"
 
    For Each oItem In Me!List25.ItemsSelected
        
    
MsgBox "dt: " & [Forms]![drawing transmittal main form]![DTNumber].Value & vbCrLf & _
"C0: " & List25.Column(0, Me!List25.ItemData(oItem) - 1) & vbCrLf & _
"C1: " & List25.Column(1, Me!List25.ItemData(oItem) - 1) & vbCrLf & _
"C2: " & List25.Column(2, Me!List25.ItemData(oItem) - 1) & vbCrLf & _
"C3: " & List25.Column(3, Me!List25.ItemData(oItem) - 1) & vbCrLf & _
"C4: " & List25.Column(4, Me!List25.ItemData(oItem) - 1) & vbCrLf
 
    ' run the code to insert the selected row
    
    'DoCmd.RunSQL "insert into [transmittal drawing list] (DTNUMber, docno) values ('" & [Forms]![drawing transmittal main form]![transmittal DT Number].Value & "','" & List25.Column(1, Me!List25.ItemData(oItem) - 1) & "');"
DoCmd.RunSQL "insert into [transmittal drawing list] (DTNUMber, docno, RevNO, doctitle, [action:]) " & _
"values ('" & [Forms]![drawing transmittal main form]![DTNumber].Value & "','" & _
List25.Column(1, Me!List25.ItemData(oItem) - 1) & "','" & _
List25.Column(3, Me!List25.ItemData(oItem) - 1) & "','" & _
List25.Column(2, Me!List25.ItemData(oItem) - 1) & "','1'" & ");"
    
 
 
    Next oItem
    're-enable messages
    'DoCmd.SetWarnings True
    
    
Else
    MsgBox "Nothing was selected from the list", vbInformation
    Exit Sub  'Nothing was selected
End If
 
End Sub

Open in new window

0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 20786229
also - i had to amend the data for the list box to add more field to populate the table...
SELECT ArchDwgList.ID, ArchDwgList.DocNo, ArchDwgList.DocTitle, ArchDwgList.DocRev FROM ArchDwgList ORDER BY ArchDwgList.DocNo; SELECT ArchDwgList.ID, ArchDwgList.DocNo, ArchDwgList.DocTitle, ArchDwgList.DocRev FROM ArchDwgList ORDER BY ArchDwgList.DocNo;

but i wasn;t behaving...
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 20956062
fine by me
0
 

Author Comment

by:LDP-bb
ID: 20958695
I don't believe there is code or a solution that has been offered that actually functions. I'm assuming "but i wasn't behaving" means  " But (it) (the code) was not behaving"  . I thought there would be another solution posted and that Sudonim was still working on it.  I'm sure I posted back that there is a replication in fields because the transmittal form is used for other documents apart from drawings and therefore has it's own table of records for each transmittal , the point was to append to this table when the user was issuing drawings only by choosing from the list of drawings. After posting at least two sample databases to illustrate the schema and to test code and explaining the problem numerous times while waiting for a solution since last November; it's clear Sudonim cannot solve the problem so I disagree with the recommendation to "Accept".
0
 

Author Comment

by:LDP-bb
ID: 20961152
I appreciate your guidance and clarification of the roles I have as a "Asker".  I understand much better now the responsibilites I have as one who asks a question. Particularly, in that context, I would like to thank Sudonim for his/her efforts. I regret I did not respond January 31with further questions and/or information as I was unsure as to what I should do at that point. I also apologize for the fact that the post has been a waste of effort for both parties as a result. I am hoping you can proceed to delete all history and/or any reference or record of this post.    

Thanks
ldp-bb
0
 
LVL 61

Expert Comment

by:mbizup
ID: 20968386
Changed recommendation:

Delete/Refund

mbizup
EE Cleanup Volunteer
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 20973730
i am happy to proceed with completing this question, i have already put a lot of effort ( a couple of hours work)  into trying to solve the problem.
I need answers to these question before i can proceed:
===============================================================================
"but i am confused - why does the Transmittal Drawing List table replicate fields from the source table ArchDwgList, plus the "approved field"

TDL table should just have its id, the dtnum, the doc num and the approved field?

surely the display on the form Drawing Transmittal Main Form should a query linking back to the ArchDwgList table?
=============================================================================

I did not realise that the user was still waiting for me.  I have a copy of the db which i can upload, but the above issue in the users data structure does not make sense to me.

I'd be disappointed to have spent a lot of time explaining, coaching and providing several different suggestions and item so of code.

woulf it be fair to say i have been trying to explain and coach the asker to a solution, rather than simply downloading their database, fixing their problem, and posting them back a solved version.

I am not sure why the recommendation has been changed.

I believe i have offered several explanations on how to actually structure and implement the solution.
11.08.2007 at 10:20AM GMT, ID: 20240081 - i provided a solid explanation of how to solve the problem
01.31.2008 at 01:16PM GMT, ID: 20786184 - i provided code based on user's db which they could have inserted into their MDB. i asked a question on what i did not understand was the underlying table structure.. but what i provided i believed solved the question.
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 20975755
no worries.
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 20975818
here is a simple example of multiselect working:

its extension is txt, but you need to rename it to .zip and then extract from it the mdb

multiselect.txt
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 20975884
try this code for the command 27 button:
Private Sub Command27_Click()
Dim oItem As Variant
Dim sTemp As String
If Me!List25.ItemsSelected.Count <> 0 Then
' hide the append notification
DoCmd.SetWarnings False
 
    For Each oItem In Me!List25.ItemsSelected
        
    On Error GoTo errtrap
    ' his msgbox code is for checking, you can remove it or make it prettier...
    
        MsgBox "dt: " & [Forms]![Drawing Transmittal Main Form]![DTNumber].Value & vbCrLf & _
        "C0: " & List25.Column(0, Me!List25.ItemData(oItem) - 1) & vbCrLf & _
        "C1: " & List25.Column(1, Me!List25.ItemData(oItem) - 1) & vbCrLf & _
        "C2: " & List25.Column(2, Me!List25.ItemData(oItem) - 1) & vbCrLf & _
        "C3: " & List25.Column(3, Me!List25.ItemData(oItem) - 1) & vbCrLf & _
        "C4: " & List25.Column(4, Me!List25.ItemData(oItem) - 1) & vbCrLf, , "show values to be inserted"
 
    ' run the code to insert the selected row
    
    'DoCmd.RunSQL "insert into [transmittal drawing list] (DTNUMber, docno) values ('" & [Forms]![drawing transmittal main form]![transmittal DT Number].Value & "','" & List25.Column(1, Me!List25.ItemData(oItem) - 1) & "');"
        DoCmd.RunSQL "insert into [transmittal drawing list] (DTNUMber, docno, RevNO, doctitle, [action:]) " & _
        "values ('" & [Forms]![Drawing Transmittal Main Form]![DTNumber].Value & "','" & _
        List25.Column(1, Me!List25.ItemData(oItem) - 1) & "','" & _
        List25.Column(3, Me!List25.ItemData(oItem) - 1) & "','" & _
        List25.Column(2, Me!List25.ItemData(oItem) - 1) & "','1'" & ");"
    
    Next oItem
    're-enable messages
    DoCmd.SetWarnings True
    
    
Else
    MsgBox "Nothing was selected from the list", vbInformation
    Exit Sub  'Nothing was selected
End If
 
errtrap:
 Select Case Err.Number
 Case 0
 Case 2450
 ' the other form which provides the transmittal id is not open
 DoCmd.OpenForm "Drawing Transmittal Main Form"
 MsgBox "please select a transmittal to append to"
 Exit Sub
 Case Else
 MsgBox Err.Number & vbCrLf & Err.Description
 End Select
 
End Sub

Open in new window

0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 20975927
this file is your db from way back when with th working append code inserted under the button of the relevant form

as before please rename the txt file to .zip and then extract the MDB
LDP-Reply.txt
0
 

Author Comment

by:LDP-bb
ID: 20976216
Sudonim:

Thanks. I really appreciate the fact we can keep this alive. I realize I caused alot of confusion with the naming of my tables and forms. I'll try to answer some of your questions. Overall, The transmittal form is used to send all documents out of a project office, not just drawings. It should have been named "Document Transmittal Form" . However, because the transmittal of drawings can include many files at one time, I wanted the user to be able to select the records form the Drawing form (Arch Dwg)and append that selected list so to speak to the overall "Transmittal Dwg (document) List" based on the primary key DTNum which they previously set-up and assign to the transmittal. That's why I replicated the field names to match for both the drawings lists and the overall document list. Because the transmittal is used for many other kinds of documents besides drawings I didn't consider useing a control on the Transmittal form itself to issue drawings as this happens maybe only 30% of the time. The "Transmittal Drawing List" table is list of records of all documents transmitted with a particular Primary Key DTNum. All documents , not just drawings, get added to this table. This table should have been called Transmittal Document List. The "For Approval" item is one option of the "Action" to be taken for who receives the document. Similarly, the "Drawing Transmittal Table" should have been named "Document Transmittal Table" , it also has the Primary 'DTNum" but it lists just the metadata particular to each transmittal or DT, (Document Transmittal) ie , the date of the transmittal, the subject, who it went to, if there was a digital file of the document sent (DTElec) etc.  I see you posted many replies since I started this one, I'll try to review what you have added to-day and get back to you as soon as I can.
By the way, thanks again Sudonim for this educational approach.
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 21012076
i see.  I was not sure because you seemed to be replicating fields of exact data which might not be required..... e.g. if you link drawings to a transmittal, there is lots of data copied accross...if someon else then goes and updates some of those fields for the original drawing your data is then out of synch.  it could be that thi is part of your requirements, but it "feels" like a possible problem.

if you only link the drawing ID to the transmittal, then use a query to return the drawing details by linking on its id, when you want to display the info, you ensure referenial integrity and data accuracy.

thats why in my examples i only  used a "link" table to store the users selected drawings ID's with the Transmittal ID... but i asked the question because in your DB you bring a lot more data over.

i think i have gottenit working your way in the db above.

let me know how it goes.
0
 

Author Comment

by:LDP-bb
ID: 21014066
I've been totally swamped the last few days and haven't had time to review everything. It makes sense what you mention about using the ID however the project may issue the same drawing number at a later date with a different revision number. It was for that reason I wanted to append the drawing data to the transmittal document table in way that took into account this possiblity of different revisions of the same drawing being issued at different times under different Document Transmittal numbers. All documents that leave the office have a Document Transmittal sheet (with a unique Document Transmittal number) that accompanies the documents being issued. The transmittal sheet lists the documents being released under that transmittal number. The office can create reports showing when a specific document was released, listng each Transmittal number, document data, revision and date of issue. Under this schema there is going to be duplication of some data in the Transmittal Document table. If I only refer back to the ID of the document in the drawing table, I think, only the current revision numner of each document will be listed for all and any transmittal that ever refered to that document and that would not be correct as there were different versions of the same document that went out at different times. Occassionaly, staff have to recall specific historical transmittal data as well, what exactly went out at a specific time and what revision of the specific document (drawing). That was my reason for appending data to the Transmittal Document table using the DTNum as primary key for that specific date.  To be honest I have to spend more time reviewing the samples you posted however in LDP-Reply it's seems almost there , what is selected is sent to the Transmittal Document table but always under DTNum #One only and the list of drawings chosen just grows and grows each time you try to add others  to a new transmittal . It keeps an ongoing list of what was selected in the past and includes those in the transfer. In the Multiselect sample you posted and there is only one form I've never seen and I'm not sure what is does. See the files attached regarding forms in your samples. Thanks Sudonim , I'll get back with some more questions. I've got to get time to review and understand the code,thansk for your patience with this.
Database-Dwg-to-Transmittal.jpg
Database-Multiselect-test.pdf
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 21014356
okj, so the dtnum fields dupication is deliberate - but could be  averted by only appending the dtnum and revision number as if those were the primary keys...

there was an error in my logic.  this version shows and appends the correct records:
Private Sub Command27_Click()
Dim oItem As Variant
Dim sTemp As String
If Me!List25.ItemsSelected.Count <> 0 Then
' hide the append notification
DoCmd.SetWarnings False
 
    For Each oItem In Me!List25.ItemsSelected
        MsgBox oItem
    On Error GoTo errtrap
    ' his msgbox code is for checking, you can remove it or make it prettier...
    
'        MsgBox "dt: " & [Forms]![Drawing Transmittal Main Form]![DTNumber].Value & vbCrLf & _
'        "C0: " & List25.Column(0, Me!List25.ItemData(oItem) - 1) & vbCrLf & _
'        "C1: " & List25.Column(1, Me!List25.ItemData(oItem) - 1) & vbCrLf & _
'        "C2: " & List25.Column(2, Me!List25.ItemData(oItem) - 1) & vbCrLf & _
'        "C3: " & List25.Column(3, Me!List25.ItemData(oItem) - 1) & vbCrLf & _
'        "C4: " & List25.Column(4, Me!List25.ItemData(oItem) - 1) & vbCrLf, , "show values to be inserted"
'
        MsgBox "dt: " & [Forms]![Drawing Transmittal Main Form]![DTNumber].Value & vbCrLf & _
        "C0: " & List25.Column(0, oItem) & vbCrLf & _
        "C1: " & List25.Column(1, oItem) & vbCrLf & _
        "C2: " & List25.Column(2, oItem) & vbCrLf & _
        "C3: " & List25.Column(3, oItem) & vbCrLf & _
        "C4: " & List25.Column(4, oItem) & vbCrLf, , "show values to be inserted"
 
    ' run the code to insert the selected row
    
    'DoCmd.RunSQL "insert into [transmittal drawing list] (DTNUMber, docno) values ('" & [Forms]![drawing transmittal main form]![transmittal DT Number].Value & "','" & List25.Column(1, Me!List25.ItemData(oItem) - 1) & "');"
'        DoCmd.RunSQL "insert into [transmittal drawing list] (DTNUMber, docno, RevNO, doctitle, [action:]) " & _
'        "values ('" & [Forms]![Drawing Transmittal Main Form]![DTNumber].Value & "','" & _
'        List25.Column(1, Me!List25.ItemData(oItem) - 1) & "','" & _
'        List25.Column(3, Me!List25.ItemData(oItem) - 1) & "','" & _
'        List25.Column(2, Me!List25.ItemData(oItem) - 1) & "','1'" & ");"
        
         DoCmd.RunSQL "insert into [transmittal drawing list] (DTNUMber, docno, RevNO, doctitle, [action:]) " & _
        "values ('" & [Forms]![Drawing Transmittal Main Form]![DTNumber].Value & "','" & _
        List25.Column(1, oItem) & "','" & _
        List25.Column(3, oItem) & "','" & _
        List25.Column(2, oItem) & "','1'" & ");"
    
    Next oItem
    're-enable messages
    DoCmd.SetWarnings True
    
    
Else
    MsgBox "Nothing was selected from the list", vbInformation
    Exit Sub  'Nothing was selected
End If
 
errtrap:
 Select Case Err.Number
 Case 0
 Case 2450
 ' the other form which provides the transmittal id is not open
 DoCmd.OpenForm "Drawing Transmittal Main Form"
 MsgBox "please select a transmittal to append to"
 Exit Sub
 Case Else
 MsgBox Err.Number & vbCrLf & Err.Description
 End Select
 
End Sub

Open in new window

LDP-Reply.txt
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 21039753
any joy on the attached?
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 21124487
any joy yet?
0
 

Author Comment

by:LDP-bb
ID: 21129156
Sudionim:

Thanks, I'm still swamped on three projects. I'll try it out this weekend and get back Monday.

Thanks for patience once more.
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 21141019
no worries.
0
 

Author Comment

by:LDP-bb
ID: 21155360
sudonim..here is a flash of joy....it seems to partially work, i have to look at the code in detail but I'm getting pop-up windows, that I don't understand and are meaningless but the transmittal list does get appended BUT if I close all the forms and re-open them the data gets sent to the sub-form of the main transmittal form and all is well. i have to close and re-open the forms to update the sub-form I guess. I have to spend time on the code but like I siad there is definately a ray of joy there.

Thanks sudonim

see the attached file.
Sudonim2.pdf
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 21169154
comment out the message boxes on line 9 and 20 above.  they were for testing purposes so you can see that it is picking up the correct data...

we can send a refresh to the subform spo you don;t have to re-open it. i'll have a look.

Now the only pop up is at the end saying that "x items have ben added to dtnumber Y"

if you donlt want that you can disable it. - its the msgbox after
"Case 0
    If icount > 0 Then
"
Private Sub Command27_Click()
Dim oItem As Variant
Dim sTemp As String
Dim checkdtnum As Integer
Dim icount As Integer
icount = 0
If Me!List25.ItemsSelected.Count <> 0 Then
' hide the append notification
    DoCmd.SetWarnings False
    On Error GoTo errtrap
    'check that DTMF is open
    checkdtnum = Forms![Drawing Transmittal Main Form]!DTNumber.Value
 
    For Each oItem In Me!List25.ItemsSelected
      icount = icount + 1
        DoCmd.RunSQL "insert into [transmittal drawing list] (DTNUMber, docno, RevNO, doctitle, [action:]) " & _
        "values ('" & checkdtnum & "','" & _
        List25.Column(1, oItem) & "','" & _
        List25.Column(3, oItem) & "','" & _
        List25.Column(2, oItem) & "','1'" & ");"
    
    Next oItem
    're-enable messages
    DoCmd.SetWarnings True
    
    Forms![Drawing Transmittal Main Form]![Drawing List].Requery
Else
    MsgBox "Nothing was selected from the list", vbInformation
    Exit Sub  'Nothing was selected
End If
 
errtrap:
Select Case Err.Number
Case 0
    If icount > 0 Then
        MsgBox icount & " items added to Transmittal (DT) Number " & checkdtnum
    End If
Case 2450
    ' the other form which provides the transmittal id is not open
    'MsgBox Err.Number & vbCrLf & Err.Description
    DoCmd.OpenForm "Drawing Transmittal Main Form"
    MsgBox "please select a transmittal to append to"
    Exit Sub
Case Else
    MsgBox Err.Number & vbCrLf & Err.Description
End Select
 
End Sub

Open in new window

0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 21169167
here is the txt of the zip of the mdb
LDP-Reply.txt
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 21258599
Question reopened for regrade per asker request:
http://www.experts-exchange.com/Q_23285790.html

Jason1178, CA
0
 
LVL 15

Accepted Solution

by:
Simon Ball earned 2000 total points
ID: 21260890
nice one.
in the bgrade email i got his:  
"I'll study the code in more detail but this is perfect. I'll work on adding code to take the DTNumber from the transmittal form and append it to the records of the chosen drawings. Each drawing has a field for it';s latest DTNumber. Thank you once again for patience and guidance in this. I partially understand the code so I'll review this detail. Thanks very much. " "

you just need an update query to pick up the dtnumber from one form, and plug in the current drawing inside the loop...

so on line 21... something like:

not the new variable updArch is declared... then gets the sql set to it, then gets run.


Dim oItem As Variant
Dim sTemp As String
Dim checkdtnum As Integer
Dim icount As Integer
Dim updArch As String
icount = 0
If Me!List25.ItemsSelected.Count <> 0 Then
' hide the append notification
    DoCmd.SetWarnings False
    On Error GoTo errtrap
    'check that DTMF is open
    checkdtnum = Forms![Drawing Transmittal Main Form]!DTNumber.Value
 
    For Each oItem In Me!List25.ItemsSelected
      icount = icount + 1
        DoCmd.RunSQL "insert into [transmittal drawing list] (DTNUMber, docno, RevNO, doctitle, [action:]) " & _
        "values ('" & checkdtnum & "','" & _
        List25.Column(1, oItem) & "','" & _
        List25.Column(3, oItem) & "','" & _
        List25.Column(2, oItem) & "','1'" & ");"
        
        'update [ArchDwgList] set dtnumber = " & checkdtnum & " where [DocNo] = '" & List25.Column(1, oItem) & "';"
        updArch = "UPDATE ArchDwgList SET ArchDwgList.DTNumber = " & Left("""", 1) & checkdtnum & Left("""", 1)
        updArch = updArch & " WHERE (((ArchDwgList.DocNo)=" & Left("""", 1) & List25.Column(1, oItem) & Left("""", 1) & "));"
        'use msgbox to test created sql visually
        'MsgBox updArch
        DoCmd.RunSQL updArch
    
    Next oItem
    're-enable messages
    DoCmd.SetWarnings True
    
    Forms![Drawing Transmittal Main Form]![Drawing List].Requery
Else
    MsgBox "Nothing was selected from the list", vbInformation
    Exit Sub  'Nothing was selected
End If
 
errtrap:
Select Case Err.Number
Case 0
    If icount > 0 Then
        MsgBox icount & " items added to Transmittal (DT) Number " & checkdtnum
    End If
Case 2450
    ' the other form which provides the transmittal id is not open
    'MsgBox Err.Number & vbCrLf & Err.Description
    DoCmd.OpenForm "Drawing Transmittal Main Form"
    MsgBox "please select a transmittal to append to"
    Exit Sub
Case Else
    MsgBox Err.Number & vbCrLf & Err.Description
End Select
 
End Sub

Open in new window

0
 

Author Closing Comment

by:LDP-bb
ID: 31444805
Thanks Sudonim. again, thanks for sticking with this one. I'm not an expert in VBA or SQL so this is a valuable lesson for me.  I appreciate your guidance.
Best regards, LDP
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question