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

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.

Replies To: Access 2010 - Two Questions

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.

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