After Insert Event preventing label printing???

[SyBeerianTyGRRRR]

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&quot

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

 

[SyBeerianTyGRRRR]

Hmmmm!! Heres a funny thing,

I have just managed to get this to work by reversing the process i.e. i have attached the After Insert Event code to frmContacts rather than frmPlmkrs and this works fine, prints labels o.k. and everything!! Not sure yet if this is going to be a suitable solution so can anyone still give me a pointer as to what the problem may be with the original intended method???

Thanks v much

 

[SyBeerianTyGRRRR]

Ha, didn't think anyone would fancy delving into that one. Well for info i have fixed the problem, it lay in one of the fields of the query that populated the Temp table used to send the relevant contact details to the report that prints out the labels!!!

Oh well never mind, learnt a lot in figuring it out though.

See ya.