3 Replies - 3752 Views - Last Post: 13 October 2012 - 09:02 PM

#1 Wulfen  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 16-December 08

Access 2010 - Two Questions

Posted 07 October 2012 - 10:18 AM

Okay, thanks to scouring the web I have been able to find a lot of useful info on how to build this database for work, however I have come to two points I can't seem to get working and I am not sure how to proceed with them.

1st: We have a list of people who are assigned a docket number, along with a lot of other information that goes with the project they are assigned to, however in the spreadsheet they are listed as John Smith; Jack Smith etc. I have the spreadsheet loaded to three primary tables, one being the majority of the project information, the second being the department management and finally the third being for these guys. However my boss wants to make it where they can be tracked on an individual level. How can I split the record up further once I have set it to the new table and still keep them assigned to the correct docket number?

2nd: Probably a simpler one, I have a form that I use to assign various items to the people in the same project, right now it is just a small modal form with a bunch of tick boxes, but I want the application to write out the variables for them on the main form, But I am at a loss as to how to write the array for this, I have found some documentation on how to get it to another form using an open variable but then I get lost with the tick boxes and how to handle them.


Thanks for your help as always.

Is This A Good Question/Topic? 0
  • +

Replies To: Access 2010 - Two Questions

#2 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Access 2010 - Two Questions

Posted 08 October 2012 - 12:00 AM

1. What do you mean by 'have the spreadsheet loaded to three primary tables'? How is a spreadsheet involved? A docket can have any number of people assigned? If you want to relate people assigned to a docket, probably need another table.

2. Don't understand what you mean by 'write out the variables for them on the main form'. Why does data from a modal form need to be written to a 'main' form? A bunch of yes/no fields is an indicator of non-normalized data structure.

Do you want to provide the db for analysis?
Was This Post Helpful? 0
  • +
  • -

#3 Wulfen  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 16-December 08

Re: Access 2010 - Two Questions

Posted 13 October 2012 - 07:18 PM

Okay, I managed to figure out how to get the peoples names taken from the list separated by the semicolon, but now I need to know how to get this bit of code to work correctly. It takes information from the temp table (a copy of the main table that is deleted once the user saves the document however it currently only copies the information from the first record to all the records in the table. I got it to stop doing that by looking for the ID number first then copying the information, however I can't figure out how to get it to do that for every record in the temp table.

Private Sub Command17_Click()
On Error GoTo Error_Handler
    Dim db          As DAO.Database
    Dim rs          As DAO.Recordset
    Dim iCount      As Integer
 
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("Table1", dbOpenDynaset) 'open the recordset for use (table, Query, SQL Statement)
DoCmd.SetWarnings False 'Turn off warnings

    With rs
        If .RecordCount <> 0 Then 'Ensure that there are actually records to work with
            'The next 2 line will determine the number of returned records
            rs.MoveLast 'This is required otherwise you may not get the right count
            iCount = rs.RecordCount 'Determine the number of returned records
            
            Do While Not .BOF
If rs![ID] = Me.ID Then  'stops to check if the number matches
rs.Edit
rs![Test] = Me.Test 'Just test data, but currently copies to the correct field
rs![Ton] = Me.Ton   'Just test data  but currently copies to the correct field
rs.Update
End If

'-----------------------------------------------------------------------
'This is Looping to all the records, but is not correctly updating them
'currently this is only updating the records with the current record ID
'the form is set to looking at.
'-----------------------------------------------------------------------
                              
                              
                              
                              
               .MovePrevious
              
                               
            Loop
            
            
        End If
        
        
    End With
   
    rs.Close 'Close the recordset

Error_Handler_Exit:
    On Error Resume Next
    'Cleanup after ourselves
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
 
Error_Handler:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: LoopRecExample" & vbCrLf & "Error Description: " & _
    Err.Description, vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
    DoCmd.SetWarnings True 'Turn off warnings
End Sub




Was This Post Helpful? 0
  • +
  • -

#4 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Access 2010 - Two Questions

Posted 13 October 2012 - 09:02 PM

I don't understand what you are trying to do. Why a 'temp' table? You want to update what table? What is the criteria for determining which records to update?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1