Compile error / Form Control - Please Help!

[d9pierce]

Hi all,
I have a form called Frm_Company_Main and on that I have a button to
add a new contact with the following code attached to it. I want to add
a new contact and carry the values of the CompanyID and ContactTypeID
over to that form. This code works well by its own but clashes with the
next set of code that I attached to my Frm_Add_Contact. Now, if I open
add contacts it works just fine to add, delete... but when I use the
button from the Frm_Company_Main form, i get a compile error.

Could anyone help with how to change the code attached to the
Frm_Add_Contact so that it will allow me to use this control button and
open to a new record with the CompanyID and ContactTypeID from my other
form? Please help if you can. I have spent most of the night reviewing
previous posts and I see nothing in relation to this. I just want to be
able to open this form to add a new record to a Company contact without
loosing my transaction status.

The Frm_Add_Contact will only be opened from the the Command Button on
the Frm_Company_Main. Should I maybe use different code to commit or
not commit changes? If so, could you give me an example as I am new to
most of this.

Thanks so much!

Frm_Company_Main

Private Sub Add_New_Click()
DoCmd.OpenForm "Frm_Add_Contact", dataMode:=acFormAdd
With Forms("Frm_Add_Contact")
!ContactTypeID = Me.ContactTypeID
!CompanyID = Me.CompanyID
End With
End Sub

-------------------------------------------------------------------------------
Frm_Add_Contact (This is the compile error section) There is more code
if you need it and I will be happy to send!

Private Sub Form_Open(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM Tbl_Contact", dbOpenDynaset)
Set Me.Recordset = rs
Set rs = Nothing
Set db = Nothing
End Sub

Private Sub Form_Unload(Cancel As Integer)
Dim msg As Integer
If Me.Saved Then
msg = MsgBox("Do you want to commit all changes?", vbYesNoCancel)
Select Case msg
Case vbYes
DBEngine.CommitTrans
Case vbNo
DBEngine.Rollback
Case vbCancel
Cancel = True
End Select
Else
If Me.Dirtied Then DBEngine.Rollback
End If

End Sub

 

[Rick Brandt]

Hi all,
I have a form called Frm_Company_Main and on that I have a button to
add a new contact with the following code attached to it. I want to
add a new contact and carry the values of the CompanyID and
ContactTypeID over to that form. This code works well by its own but
clashes with the next set of code that I attached to my
Frm_Add_Contact. Now, if I open add contacts it works just fine to
add, delete... but when I use the button from the Frm_Company_Main
form, i get a compile error.

Could anyone help with how to change the code attached to the
Frm_Add_Contact so that it will allow me to use this control button
and open to a new record with the CompanyID and ContactTypeID from my
other form? Please help if you can. I have spent most of the night
reviewing previous posts and I see nothing in relation to this. I
just want to be able to open this form to add a new record to a
Company contact without loosing my transaction status.

The Frm_Add_Contact will only be opened from the the Command Button on
the Frm_Company_Main. Should I maybe use different code to commit or
not commit changes? If so, could you give me an example as I am new to
most of this.

Thanks so much!

Frm_Company_Main

Private Sub Add_New_Click()
DoCmd.OpenForm "Frm_Add_Contact", dataMode:=acFormAdd
With Forms("Frm_Add_Contact")
!ContactTypeID = Me.ContactTypeID
!CompanyID = Me.CompanyID
End With
End Sub

-------------------------------------------------------------------------------
Frm_Add_Contact (This is the compile error section) There is more code
if you need it and I will be happy to send!

Private Sub Form_Open(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM Tbl_Contact", dbOpenDynaset)
Set Me.Recordset = rs
Set rs = Nothing
Set db = Nothing
End Sub

Private Sub Form_Unload(Cancel As Integer)
Dim msg As Integer
If Me.Saved Then
msg = MsgBox("Do you want to commit all changes?", vbYesNoCancel)
Select Case msg
Case vbYes
DBEngine.CommitTrans
Case vbNo
DBEngine.Rollback
Case vbCancel
Cancel = True
End Select
Else
If Me.Dirtied Then DBEngine.Rollback
End If

End Sub

Why are you using a Recordset instead of just binding your contacts form to the
contacts table? Then you could get rid of all that stuff in the close event.

If you are doing all of this just so you can "control" when the contacts get
saved then I have to ask WHY? Your user has pressed a button labelled "Add
Contacts", has filled out data, and then is closing the form. Why on earth
would they NOT want to save?

You're causing yourself needless trouble for no real benefit. Teach them to use
the Escape key and move on to something useful.

 

[d9pierce]

Hi Rick,
Thanks! I fixed this! OK, now thisis the situation I ran into.

The add contact form and button works well. The only thing it doesnt do
is fill the listbox with the contacts until after I click on the
transfered cbo. Then the list is populated. Is ther a way to get the
list populated with the current transfered info before I have to click
on something. I have tried about 50 different things and I cannot get
it to work correctly. Any suggestions?

Dave

 

[strive4peace]

Hi Dave

Aside from removing the form OPEN code (and making the
RecordSource --> Tablename) behind each of the two forms ...

remove CAPTION for ContactID in the table design

the way your forms are currently set up:

in the form Frm_Contact_Main:

make sure the NAME property of the control containing
ContactID is ContactID

in the form Frm_Company_Main:

make sure the NAME property of the control containing
CompanyID is --> cboCompanyID

make the BOUND column of ListContacts the first column (1)
and list ContactID first

RowSource -->
SELECT DISTINCT contact.ContactID, conType.CTypeExtID,
comp.[Contact Name], conType.ContactType, contact.Phone,
contact.Extension, contact.Fax, contact.EMail
FROM TBL_Contact_Type AS compType RIGHT JOIN (Tbl_Contacts
AS [comp] INNER JOIN (TBL_Contact_Type_Extended AS conType
RIGHT JOIN TBL_Contact_Contacts AS contact ON
conType.CTypeExtID = contact.CTypeExtID) ON comp.CompanyID =
contact.CompanyID) ON compType.ContTypeID = comp.ContactTypeID
WHERE
(((contact.CompanyID)=[Forms]![Frm_Company_Main]![cboCompanyID]));

Boundcolumn -->1
ControlSource --> (nothing)
Columncount --> 8
ColumnWidths --> 0";0";0";1";1";0.5";1";1.75"
Width --> 5.3

you SHOULD be able to do this

Private Sub ListContacts_DblClick(Cancel As Integer)
If IsNull(Me.ListContacts) Then Exit Sub
DoCmd.OpenForm "Frm_Contact_Main", , , _
"ContactID = " & Me.ListContacts
End Sub

I made several other changes and finally got it to work
(like commenting all of your code behind each form -- it
could be added back and tested as needed)... but this is the
essence of what I believe that you need to do.

you should have a way on your company form to add a contact
such as a combobox

To ease confusion, your tables need to be renamed:

tbl_Contacts --> tbl_Companies
with primary key CompanyID
(rename this field in related tables and don't use Lookups
in the table design)

tbl_Contact_Contacts --> tbl_Contacts

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com