vba ContactForm embedded on a MainForm

[Susy]

Hi there,

I created this main form which has many input drop-down fields. One of those drop-down fields is the contact field.

The contact field is a table. The main form is a table which by ID is connected to the other tables (like contact table).

The problem I have now is:

If I were to delete one contact from the table (let say I created another form where the user can delete or modify the contact), then this contact is also gone from the main table.

BUT, I DON't want that. Of course, modifications are allowed, so if someones last name changes, then this change should be reflected on the main table (mainform), but If I delete contacts which are not active anymore
but I still want to have there past information, then I shoould be able to delete them without deleting them from the maintable.

A hint how to do that? Delete contacts from the contact table but not from the maintable?

Regards!

[John_G]

If I delete contacts which are not active anymore but I still want to have there past information, then I shoould be able to delete them without deleting them from the maintable.

That doesn't make sense. If you delete a contact from the contacts table, then there is nothing (no record) for the main table to "point to", so you don't see any data. The contact information isn't in the main table, only a link to it is.

The easiest way to prevent accidental loss of data is to create a relationship between the main table and the contacts table (it is probably one-to-many, but which is the "one" side in your case? ). The relationship should renforce referential integrity, but NOT allow cascade delete. With this arrangement, if you or someone else tries to delete a contact record that is referenced by a record in the main table, Access won't allow it, and give you an error message.

If you need to keep historical data, just don't delete it. There is nothing wrong with having contact information that is not referenced anywhere in the main table.

[Susy]

Hi John_G,

thanks... I still trying to follow (due to my lack of knowledge).

When I delete something from the contacttable, it is also deleted in the maintable, till now. That is bad.

Still thinking.......

Regards!

[June7]

Have a field that flags contact as active or inactive. Then use filter to exclude inactive records.

[Susy]

Hi Juna, sorry to ask... what do you mean with "flag a field as active or inactive" ?

Is it like creating a drop-down menu where you create a field "HideShow" ( 0 or 1 ) and then create a query?

Regards!

[John_G]

When I delete something from the contacttable, it is also deleted in the maintable, till now. That is bad.

No, it isn't "deleted" from the main table, because it is not in the main table to begin with. Only a link (Contact_ID, perhaps?) to is, so when you delete a contact, the main table/form has a value that doesn't link anything. At least, that is the way it should be designed. Can you post the design of your main table?

[June7]

I mean a Yes/No or text type field that can be used to apply filter criteria so users are only offered active records to select. So a form or report RecordSource or combobox RowSource could be like: SELECT ContactID, ContactName FROM Contacts WHERE IsActive = True;

[Susy]

I did that... the problem is that the contact which I set as inactive cannot be seeing in the mainform (contact-drop-down menu). When I check the table main, the contact is there but not in the drop-down menu... and that is not nice... as I said, the drop-down menu is not empty it is just "empty but containing the contact which is now inactive.

my Contact drop-down menu in the mainform as this properties:
1. Control Source: ContactID

2. Row Source:
SELECT [ContactFName] & " " & [ContactLName] AS SOrgName, ContactPNumber, ContactID, IsInactive
FROM tblContact
WHERE (((tblContact.IsInactive)=False));

3.RowSourceType: Table/Query

4.BoundColumn: 3

Any Ideas of how to fix this issue?

But when I think it makes sense... the form just shows only those which are contact which are active...

Regards,

[Susy]

As you will see Contacts No 2 and Contacts No 3 cannot be found on the mainreport... See attachment...
Contactform gives the user the possibility to set contacts as active and inactive.

This is just an idea what i am trying to avoid... to get empty fields from past records... you will understand and you see it :-)

Thanks in advance.

[June7]

First, ContactID field in tbl_Main must be a number type because it is storing the ContactID from tbl_Contacts which is an autonumber.

Change main form RecordSource to:
SELECT tbl_Main.ReportID, tbl_Main.ReportNumber, tbl_Main.ContactID, [ContactFName] & " " & [ContactLName] AS CN
FROM tbl_Contact RIGHT JOIN tbl_Main ON tbl_Contact.ContactID = tbl_Main.ContactID;

Bind a textbox to the calculated CN field to display contact full name. Set the textbox as Locked Yes and TabStop No. The textbox can sit next to or on top of the combobox. If it is on top, user won't be able to click into the combobox. Could use code in textbox GotFocus event to immediately set focus to the combobox.

There is no reason for user to see the ReportID field. Don't even need this on the form. Same for the ContactID on Contacts form.

[Susy]

Hi June,

thanks, that is a nice approach.

1. Question: Do I need to modify my table main as well... what I mean it that in tbl_Main in Design View I change the display control to: Combo Box, Row Source Type: Table/Query and RowSource to: SELECT [contactfname] & " " & [contactlname] AS ContactName, tbl_Contact.ContactPNumber, tbl_Contact.ContactID FROM tbl_Contact;

...because otherwise I get only one number (contact id) and not full contact name. Can you see the tbl_Main --- ContactID field?

2. Question: In my main table (main form) I have at least 55 field (input fields, drop down menus) and not only the contact name can be set to inactive... there are other fields as well... and some fields are within a subform... which makes things -- I don't know yet, --- most likely complicated... anyway, the question is... Do I need to include every field in the Main Form RecordSource....? Instead of Select * from tbl_Main, ... I need to include every field I am using, creating...

Thanks in advance!

[June7]

1. I never build lookups in table because I want to see the actual stored value when I view tables. Since users do not work directly with tables there is really no reason to show the alias.

2. You have more lookup tables? You did not provide structure with 55 fields and other lookup tables. Apply the technique where needed. Yes, would have to include the other lookup tables in the query. Alternative is to use DLookup expression in textboxes. However, domain aggregate functions can perform slowly on form.

[Susy]

Yes, thanks, it is working great. Thanks again.

to point 1 .... the query is in the table_Main in the field ContactID...

If I leave field ContactID as text then it would look like this:


but when change to a combobox and with the query posted above.. it looks like this:




Should I modify the tables as well?

Nice weekend!

[June7]

That's up to you. I explained my preference and why. Do what works best for you. If you use the filtered RowSource in table, alias will not show for the 'inactive'. So, no, don't need to do the same setup for the table.

[Susy]

You are right... either way when I query the information I get the ids and not the text directly...

Thanks