SyBeerianTyGRRRR
Technical User
Hi folks,
Well here goes, i have a problem that has arisen out of a solution provided by responses to an earlier post of mine. The previous post was entitled "linking two tables???" and was posted a couple of days ago. Basically i wanted to be able to simultaneously input the same data into two separate tables from a single form called:
frmPlmkrsDbse
the two tables were:
tblPlmkrs (the record source for the above form)
and
tblContacts (were i keep contact details for all clients, customers etc)
After receiving several tips from some very helpful people on the forum i achieved this with the following code attached to the After Insert Event Procedure on frmPlmkrsDbse:
""""Start Of Code""""
Private Sub Form_AfterInsert()
Dim dbs As DAO.database, rst As DAO.Recordset
Set dbs = CurrentDb()
Set rst = dbs.openrecordset("tblContacts"
With rst
.addnew
!Salutation = Me.Salutation
!FirstName = Me.FirstName
!LastName = Me.LastName
!CompanyName = Me.CompanyName
!StreetAddress = Me.StreetAddress
!Village = Me.Village
!Town = Me.Town
!City = Me.City
!PostalCode = Me.PostalCode
.update
End With
rst.Close
End Sub
""""End Of Code"""""
Excellent!! I thought, this code now copied the data from the fields shown above straight into the corresponding fields on the other table (tblContacts)
However, and heres the problem. The above code is somehow interfering with or preventing another function of the database from operating.
Are you still with me????
The problem is that on the form frmContacts that has tblContacts as its record source i have a command button that opens a form called frmSelectContactsForLabels which has a Multi Select List Box from which users can select multiple Contacts and then by cicking a command button on frmSelectContactsForLabels print off a sheet of mailing labels for those contacts using the following code attached to the PrintLabels button:
""""Start Of Code""""
Private Sub cmdPrintLabels_Click()
On Error GoTo ErrorHandler
Dim strSQL As String
Dim lst As Access.ListBox
Dim strName As String
Dim strAddress As String
Dim strJobTitle As String
Dim strTestFile As String
Dim varItem As Variant
Dim intIndex As Integer
Dim intCount As Integer
Dim intRow As Integer
Dim intColumn As Integer
Dim strTest As String
Dim i As String
Dim lngID As Long
Set lst = Me![lstSelectContacts]
'Clear old temp table
DoCmd.SetWarnings False
strSQL = "DELETE * from tblContactsForLabels"
DoCmd.RunSQL strSQL
'Check that at least one contact has been selected
If lst.ItemsSelected.Count = 0 Then
MsgBox "Please select at least one contact"
lst.SetFocus
Exit Sub
End If
For Each varItem In lst.ItemsSelected
'Check for required address information
strTest = Nz(lst.Column(5, varItem))
Debug.Print "Street address: " & strTest
If strTest = "" Then
Debug.Print "Can't send letter -- no street address!"
Exit Sub
End If
strTest = Nz(lst.Column(6, varItem))
Debug.Print "City: " & strTest
If strTest = "" Then
Debug.Print "Can't send letter -- no city!"
Exit Sub
End If
strTest = Nz(lst.Column(8, varItem))
Debug.Print "Postal code: " & strTest
If strTest = "" Then
Debug.Print "Can't send letter -- no postal code!"
Exit Sub
End If
'All information is present; write a record to the temp table
lngID = lst.Column(0, varItem)
Debug.Print "Selected ID: " & lngID
strSQL = "INSERT INTO tblContactsForLabels (ContactID, FirstName, " _
& "LastName, Salutation, StreetAddress, Town, City, StateOrProvince, " _
& "PostalCode, Country, CompanyName, JobTitle )" _
& "SELECT ContactID, FirstName, LastName, Salutation, " _
& "StreetAddress, Town, City, StateOrProvince, PostalCode, Country, " _
& "CompanyName, JobTitle FROM tblContacts " _
& "WHERE ContactID = " & lngID & ";"
DoCmd.RunSQL strSQL
Next varItem
'Print report
DoCmd.OpenReport reportname:="rptContactLabels", View:=acViewPreview
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
""""End Of Code""""
The problem is that this print labels button will not recognise data that has been inputted via the After Insert Event method on frmPlmkrsDbse it will only now print labels for Contact data that is inputted directly into frmContacts, thus defeating the object of my earlier achievment (thanks to help from others of course!!)
Well there you have it, hope someone out there could be be bothered to stick with me there. And hopefully someone can give me a pointer as to what is causing the problem. I will pay in gold bullion if someone can solve this!!!
Hope someone fancies a challenge, many thanks in advance..
Cheers
Well here goes, i have a problem that has arisen out of a solution provided by responses to an earlier post of mine. The previous post was entitled "linking two tables???" and was posted a couple of days ago. Basically i wanted to be able to simultaneously input the same data into two separate tables from a single form called:
frmPlmkrsDbse
the two tables were:
tblPlmkrs (the record source for the above form)
and
tblContacts (were i keep contact details for all clients, customers etc)
After receiving several tips from some very helpful people on the forum i achieved this with the following code attached to the After Insert Event Procedure on frmPlmkrsDbse:
""""Start Of Code""""
Private Sub Form_AfterInsert()
Dim dbs As DAO.database, rst As DAO.Recordset
Set dbs = CurrentDb()
Set rst = dbs.openrecordset("tblContacts"
With rst
.addnew
!Salutation = Me.Salutation
!FirstName = Me.FirstName
!LastName = Me.LastName
!CompanyName = Me.CompanyName
!StreetAddress = Me.StreetAddress
!Village = Me.Village
!Town = Me.Town
!City = Me.City
!PostalCode = Me.PostalCode
.update
End With
rst.Close
End Sub
""""End Of Code"""""
Excellent!! I thought, this code now copied the data from the fields shown above straight into the corresponding fields on the other table (tblContacts)
However, and heres the problem. The above code is somehow interfering with or preventing another function of the database from operating.
Are you still with me????
The problem is that on the form frmContacts that has tblContacts as its record source i have a command button that opens a form called frmSelectContactsForLabels which has a Multi Select List Box from which users can select multiple Contacts and then by cicking a command button on frmSelectContactsForLabels print off a sheet of mailing labels for those contacts using the following code attached to the PrintLabels button:
""""Start Of Code""""
Private Sub cmdPrintLabels_Click()
On Error GoTo ErrorHandler
Dim strSQL As String
Dim lst As Access.ListBox
Dim strName As String
Dim strAddress As String
Dim strJobTitle As String
Dim strTestFile As String
Dim varItem As Variant
Dim intIndex As Integer
Dim intCount As Integer
Dim intRow As Integer
Dim intColumn As Integer
Dim strTest As String
Dim i As String
Dim lngID As Long
Set lst = Me![lstSelectContacts]
'Clear old temp table
DoCmd.SetWarnings False
strSQL = "DELETE * from tblContactsForLabels"
DoCmd.RunSQL strSQL
'Check that at least one contact has been selected
If lst.ItemsSelected.Count = 0 Then
MsgBox "Please select at least one contact"
lst.SetFocus
Exit Sub
End If
For Each varItem In lst.ItemsSelected
'Check for required address information
strTest = Nz(lst.Column(5, varItem))
Debug.Print "Street address: " & strTest
If strTest = "" Then
Debug.Print "Can't send letter -- no street address!"
Exit Sub
End If
strTest = Nz(lst.Column(6, varItem))
Debug.Print "City: " & strTest
If strTest = "" Then
Debug.Print "Can't send letter -- no city!"
Exit Sub
End If
strTest = Nz(lst.Column(8, varItem))
Debug.Print "Postal code: " & strTest
If strTest = "" Then
Debug.Print "Can't send letter -- no postal code!"
Exit Sub
End If
'All information is present; write a record to the temp table
lngID = lst.Column(0, varItem)
Debug.Print "Selected ID: " & lngID
strSQL = "INSERT INTO tblContactsForLabels (ContactID, FirstName, " _
& "LastName, Salutation, StreetAddress, Town, City, StateOrProvince, " _
& "PostalCode, Country, CompanyName, JobTitle )" _
& "SELECT ContactID, FirstName, LastName, Salutation, " _
& "StreetAddress, Town, City, StateOrProvince, PostalCode, Country, " _
& "CompanyName, JobTitle FROM tblContacts " _
& "WHERE ContactID = " & lngID & ";"
DoCmd.RunSQL strSQL
Next varItem
'Print report
DoCmd.OpenReport reportname:="rptContactLabels", View:=acViewPreview
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
""""End Of Code""""
The problem is that this print labels button will not recognise data that has been inputted via the After Insert Event method on frmPlmkrsDbse it will only now print labels for Contact data that is inputted directly into frmContacts, thus defeating the object of my earlier achievment (thanks to help from others of course!!)
Well there you have it, hope someone out there could be be bothered to stick with me there. And hopefully someone can give me a pointer as to what is causing the problem. I will pay in gold bullion if someone can solve this!!!
Hope someone fancies a challenge, many thanks in advance..
Cheers