entering values into a form based on 2 different datasources - hel

[vandy]

Hi

I have 2 different tables which handles internal and external contact
information.

I have a form based on a query combining the 2 tables.

The feilds of 2 tables differs with the ID values eg. Internal table has the
ID field which is an autonumber and External contact table has an ID which is
of datatype Number type Replication Id system generated.

What I need to do is be able to selectively update the 2 tables using a
single form.

All internal contacts can by edited and new contacts can be added but all
external contacts can only be edited.

Ideally when the form reads or looks up data from the internal contact it
should display the edit and new button and when it looks up data from the
external contact table than only the edit button should show and the new
button disappear.

The internal table has an ID but the external table has a system generated
GUID.

can this be done. should i create a query combining both tables and set a
flag value. how to do this.
any help will be highly appreciated.

 

[Marshall Barton]

I have 2 different tables which handles internal and external contact
information.

I have a form based on a query combining the 2 tables.

The feilds of 2 tables differs with the ID values eg. Internal table has the
ID field which is an autonumber and External contact table has an ID which is
of datatype Number type Replication Id system generated.

What I need to do is be able to selectively update the 2 tables using a
single form.

All internal contacts can by edited and new contacts can be added but all
external contacts can only be edited.

Ideally when the form reads or looks up data from the internal contact it
should display the edit and new button and when it looks up data from the
external contact table than only the edit button should show and the new
button disappear.

The internal table has an ID but the external table has a system generated
GUID.

can this be done. should i create a query combining both tables and set a
flag value. how to do this.

The query to combine the tables could look like:

SELECT "Internal" As ContactType, I.ID. I.fa, I.fb, . . .
FROM internaltable As I
UNION ALL
SELECT "External", X.ID, X.fa, X.fb, . . .
FROM externaltable As X

Then the form's Current event procedure can check the
ContactType field and set the form/control properties
accordingly. E.g.
Me.btnAddNew.Visible = (Me.ContactType = "Internal")

 

[vandy]

Hi Marshall,

Thanks for your help on the query. I realized that the union works only if 2
tables have identical feilds which makes sense since both the tables are
combined. I have a situation were one of my table A contains a EntryGUID -
type number size Replication Id and another table B contains ID - Autonumber.
Both the feilds serves the same purpose it is unique and serves as a primary
key field. How to create or combine these 2 tables do i need to add in an ID
field in the table A to combine the feilds.

 

[Marshall Barton]

Thanks for your help on the query. I realized that the union works only if 2
tables have identical feilds which makes sense since both the tables are
combined. I have a situation were one of my table A contains a EntryGUID -
type number size Replication Id and another table B contains ID - Autonumber.
Both the feilds serves the same purpose it is unique and serves as a primary
key field. How to create or combine these 2 tables do i need to add in an ID
field in the table A to combine the feilds.

A GUID field can be converted to Text so you can put both in
the same query field.

SELECT "Internal" As ContactType, CStr(I.ID) As Key. I.fa,
I.fb, . . .
FROM internaltable As I
UNION ALL
SELECT "External", CStr(X.ID), X.fa, X.fb, . . .
FROM externaltable As X

 

[vandy]

Hi Marshall

Thanks for your help again. I seem to have reached a point of no return as
they say. I have a union query which works perfectly well thanks to you and i
have created a split form with the union query as the datasource.

Objective. Since the data are getting read from 2 tables what i am
attempting to do is i have a gotocontact combo box which takes the user to
the specific record. When the user clicks the name which is a hyperlink i
have a macro which opens a form to edit the values if required.

As mentioned earlier for external contacts only the edit button should be
visible and for Internal contacts edit and new button should be visible on
the form.

For a single form the new button disappears and apprears whenever the
contactType changes and works perfectly.

In the split form the problem i am facing is say from the Internal Table ID
=1 the user selects that id and the dialog box opens out for editing. Since i
have combined the tables ID=1 is repeated for external table as well and when
i attempt to select the external table the cursor goes to the internal table
ID and displays that value and skips the external table.

For the newbutton disappearing nothing seems to be happening
I tried this code in the forms current event, load event etc.

Private Sub Form_Current()
Me.btnnewcontact.Visible = (Me.ContactType = "lnternal")
End Sub

what happens is that once the ContactType internal is reached the
btnnewcontact does not get set to True . I have gone to the form properties
for the button and the visible is set to no and have had to explicitly change
it.

What exactly is happening here. I dont think the form is able to
differentiate when the contactType changes from internal table to external
table.
any help would be highly appreciated . Its been quite a struggle.
thanks

 

[Marshall Barton]

Thanks for your help again. I seem to have reached a point of no return as
they say. I have a union query which works perfectly well thanks to you and i
have created a split form with the union query as the datasource.

Objective. Since the data are getting read from 2 tables what i am
attempting to do is i have a gotocontact combo box which takes the user to
the specific record. When the user clicks the name which is a hyperlink i
have a macro which opens a form to edit the values if required.

As mentioned earlier for external contacts only the edit button should be
visible and for Internal contacts edit and new button should be visible on
the form.

For a single form the new button disappears and apprears whenever the
contactType changes and works perfectly.

In the split form the problem i am facing is say from the Internal Table ID
=1 the user selects that id and the dialog box opens out for editing. Since i
have combined the tables ID=1 is repeated for external table as well and when
i attempt to select the external table the cursor goes to the internal table
ID and displays that value and skips the external table.

For the newbutton disappearing nothing seems to be happening
I tried this code in the forms current event, load event etc.

Private Sub Form_Current()
Me.btnnewcontact.Visible = (Me.ContactType = "lnternal")
End Sub

what happens is that once the ContactType internal is reached the
btnnewcontact does not get set to True . I have gone to the form properties
for the button and the visible is set to no and have had to explicitly change
it.

What exactly is happening here. I dont think the form is able to
differentiate when the contactType changes from internal table to external
table.
any help would be highly appreciated . Its been quite a struggle.
thanks

That Current event procedure looks good to me.

I have no idea what you mean when you say
"Since i have combined the tables ID=1 is
repeated for external table"

At least some of the problems seem to be related to using a
split form. I guess that's is a new thing in A2007, which I
have not used yet.

OTOH, ignoring the button visibiliy issue, maybe your logic
to open the other form is not doing its job.

 

[vandy]

Hi Marshall,
Do you suggest i change my form layout. Regarding the ID issue since the
contactType is not getting recognized so i think, i think it is getting
confused when it sees the same ID no.

Eg.

INTERNAL TABLE

ID IntName Intphoneno IntAddress
1Neil Merrit 705-989-3544 Numet Eng,435 Cathedtal Lane, Ontario,
L9J 9E3
2 George Jack 705-083-8979 Smartsoftsys, 356 Call Drive, USA
3 Keal Long 91-983-89807 Heat System, 2354, Drive lane, Ontario K9P 3U9

EXTERNAL TABLE
EID ExtName ExtPhoneno ExtAddress
1 Phil Grace 09-8839-8908 123,Terance Lane, King City,
Ontario,K9H 89T
2 Newton John 01-8930-6839 234, Lawrance st, Ottawa, Ontario,
G9D 9K3
3 Terrance Kings 09-9300-9080 89, Reid St, Atlanta, USA

union query when combining the tables:
COMBINED
unionqry
ContactType ID IntName Intphoneno IntAddress
Internal 1 Neil Merrit 705-989-3544 Numet Eng,435 Cathedtal Lane, Ontario
Internal 2 George Jack 705-083-8979 Smartsoftsys, 356 Call Drive, USA
Internal 3 Keal Long 91-983-89807 Heat System, 2354, Drive lane, Ontario
K9P
External 1 Phil Grace 09-8839-8908 123,Terance Lane, King City,
Ontario,K9H 89T
External 2 Newton John 01-8930-6839 234, Lawrance st, Ottawa, Ontario, G9D
9K3
External 3 Terrance Kings 09-9300-9080 89, Reid St, Atlanta, USA

here the ID gets reapeated but differs by the Contact Type

I think the problem lies in the comboboxgoto which has a bound column
pointing to the Id to ensure the user goes to the specified record. In the
combo box query source i have the contactType feild also queried. The Column
width is set to 0";0";2";2" were the first 2 columns has the contactType and
Id.

I hope that answers your question on the ID confusion.

 

[Marshall Barton]

Do you suggest i change my form layout?

No, but you did say that everything worked in single form
view. This sounds like a big clue to something, but I don't
know enough about your form to see what it means.

Regarding the ID issue since the
contactType is not getting recognized so i think, i think it is getting
confused when it sees the same ID no.

INTERNAL TABLE
ID IntName Intphoneno IntAddress
1 Neil Merrit 705-989-3544 Numet Eng,435 Cathedtal Lane, Ontario, L9J 9E3
2 George Jack 705-083-8979 Smartsoftsys, 356 Call Drive, USA
3 Keal Long 91-983-89807 Heat System, 2354, Drive lane, Ontario K9P 3U9

EXTERNAL TABLE
EID ExtName ExtPhoneno ExtAddress
1 Phil Grace 09-8839-8908 123,Terance Lane, King City, Ontario,K9H 89T
2 Newton John 01-8930-6839 234, Lawrance st, Ottawa, Ontario, G9D 9K3
3 Terrance Kings 09-9300-9080 89, Reid St, Atlanta, USA

union query when combining the tables:
COMBINED
unionqry
ContactType ID IntName Intphoneno IntAddress
Internal 1 Neil Merrit 705-989-3544 Numet Eng,435 Cathedtal Lane, Ontario
Internal 2 George Jack 705-083-8979 Smartsoftsys, 356 Call Drive, USA
Internal 3 Keal Long 91-983-89807 Heat System, 2354, Drive lane, Ontario K9P
External 1 Phil Grace 09-8839-8908 123,Terance Lane, King City, Ontario,K9H 89T
External 2 Newton John 01-8930-6839 234, Lawrance st, Ottawa, Ontario, G9D 9K3
External 3 Terrance Kings 09-9300-9080 89, Reid St, Atlanta, USA

here the ID gets reapeated but differs by the Contact Type

I think the problem lies in the comboboxgoto which has a bound column
pointing to the Id to ensure the user goes to the specified record. In the
combo box query source i have the contactType feild also queried. The Column
width is set to 0";0";2";2" were the first 2 columns has the contactType and
Id.

I disagree. ***IF*** the ID number is confusing things, it
is because the code that uses the ID is not using
ContactType correctly. If you don't see the problem there,
please post that code.

 

[vandy]

Sorry Marshall i dont mean to complicate things!

This is the union query i used as the rowsource of the cbogotoContact

SELECT contactunion.ContactType, contactunion.CID, contactunion.FullName,
contactunion.JobTitle
FROM contactunion;

I have the bound column to be 2 which points to the CID

In the afterupdate event of the combobox i have a macro which
searchForRecord
Record - first
where condition = [CID]

The combo box has the name and job title of the contact. when the user
selects the name it goes to the specified record in the form.

in the forms current event i have

Private Sub Form_Current()
Me.btnnewcontact.Visible = (Me.ContactType = "Contacts Mktg")
End Sub

were Contacts Mktg = internal table
Contact Final = external table

ContactType CID EntryGUID FullName JobTitle
Contacts Final 1 {001D638C-6006-49E1-9FA1-892490D4B934}Ricky de
Souzas Specialists
Contacts Mktg 1 Elizabeth Morris
developer

Contacts Mktg 2 abc
developer


the above is the result of the union query.

Right now when i want to highlight Elizabeth Morris it opens up the dialog
box for Ricky de souzas who has same CID.
were am i going wrong

 

[vandy]

Hi Marshall,
I do not mean to confuse you!!

SELECT contactunion.ContactType, contactunion.CID, contactunion.FullName,
contactunion.JobTitle
FROM contactunion;

The above is the query which is there in the rowsource of the combo box
cboGoToContact.

In the afterUpdate event of the combo box i have a macro which is
SearchForRecord
Record First
Where Condition [CID]
In the combo box i have the FullName and JobTitle visible, so when the user
selects the specific record he would go to the row in the form which opens up
a single dialog form containing the contacts to be edited.

in the Form Current() event i have

Private Sub Form_Current()
Me.btnnewcontact.Visible = (Me.ContactType = "Contacts Mktg")
End Sub

were Contacts Mktg is my internal table and Contact Final 1 is my external
table.


CID Full Name Job Title Contact Type

1 Ricky de Souzas Specialists Contact Final 1

2 Elizabeth Morris Contact Final 1
1 Cheryl Wright Ingound Logistics Contact Mktg
2 KOPEC Korea Power Contact Mktg


Since CID is an autonumber in both the tables i get the union result like
the above.

when i want to go to Cheryl Wright the record skips and opens out Ricky de
Souzas record since they have the same CID.

I will have to try this using a single form layout and let you know but till
now were do you think i am making a mistake.

your help and patience is much appreciated.

 

[Marshall Barton]

This is the union query i used as the rowsource of the cbogotoContact

SELECT contactunion.ContactType, contactunion.CID, contactunion.FullName,
contactunion.JobTitle
FROM contactunion;

I have the bound column to be 2 which points to the CID

In the afterupdate event of the combobox i have a macro which
searchForRecord
Record - first
where condition = [CID]

This is the problem opening the form. The where condiftion
needs to take the ContactType into account. I never use
macros so here's how I would do it in the AfterUpdate event
procedure:

With Me.RecordsetClone
.FindFirst "CID = '" & Me.cbogotoContact _
& "' And ContactType = '" _
& Me.cbogotoContact.Column(0) & "' "
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With

If you use this code, be sure to change the combo box's
AfterUpdate event **property** from the name of the macro
to:
[Event Procedure]

The combo box has the name and job title of the contact. when the user
selects the name it goes to the specified record in the form.

in the forms current event i have

Private Sub Form_Current()
Me.btnnewcontact.Visible = (Me.ContactType = "Contacts Mktg")
End Sub

were Contacts Mktg = internal table
Contact Final = external table

ContactType CID EntryGUID FullName JobTitle
Contacts Final 1 {001D638C-6006-49E1-9FA1-892490D4B934}Ricky de
Souzas Specialists
Contacts Mktg 1 Elizabeth Morris
developer

Contacts Mktg 2 abc
developer

You may have a table for the contacts, but you did not do
anything to connect it to what the current event is doing.
Futhermore, nothing you have explained so far about the
button's visibility requires the use of the information in
the Contacts Mktg table.

Try changing the Current event code to:

Me.btnnewcontact.Visible = (Me.ContactType = "Internal")

 

[vandy]

Hi Marshall,

this is the 3rd time i am typing in this reply and i dont mean to spam this
site if you get this the 3rd time i am sorry about it. i am positive i saw my
reply when i clicked on the thread but now it is not there.

I did not mean to cause more confusion.

This is the query which is typed in as record source in the combo box
cmbGoToContact

SELECT contactunion.ContactType, contactunion.CID, contactunion.FullName,
contactunion.JobTitle
FROM contactunion;

In the afterUpdate section of the combo form i have a macro typed in which is

searchForRecord
Record : First
were condition = [CID].

This enables the user to select the name and jobtitle and go to the specific
record.

CID Full Name Job Title ContactType

1 Ricky de Souzas Specialists Contact Final 1
2 Elizabeth Morris Contact Final 1
1 Cheryl Wright Ingound Logistics Contacts Mktg
2 KOPEC Korea Power Engineering Contact Mktg


The 2 tables are Contact Final 1 which is my external table and Contact Mktg
which is my internal table. Both have a CID which is an autonumber.

When i want to go to Cherly Wright record the cursor skips and goes to Ricky
De Souzas record.

In my Form Current() event i have
Private Sub Form_Current()
Me.btnnewcontact.Visible = (Me.ContactType = "Contacts Mktg")
End Sub

I have not tried this with changing the form layout. The new button once
diappeared does not appear again.

were am i going wrong.

really appreciate your time and patience.

 

[vandy]

Hi Marshall,

this is the 3rd time i am typing in this reply and i dont mean to spam this
site if you get this the 3rd time i am sorry about it. i am positive i saw my
reply when i clicked on the thread but now it is not there.

I did not mean to cause more confusion.

This is the query which is typed in as record source in the combo box
cmbGoToContact

SELECT contactunion.ContactType, contactunion.CID, contactunion.FullName,
contactunion.JobTitle
FROM contactunion;

In the afterUpdate section of the combo form i have a macro typed in which is

searchForRecord
Record : First
were condition = [CID].

This enables the user to select the name and jobtitle and go to the specific
record.

CID Full Name Job Title ContactType

1 Ricky de Souzas Specialists Contact Final 1
2 Elizabeth Morris Contact Final 1
1 Cheryl Wright Ingound Logistics Contacts Mktg
2 KOPEC Korea Power Engineering Contact Mktg


The 2 tables are Contact Final 1 which is my external table and Contact Mktg
which is my internal table. Both have a CID which is an autonumber.

When i want to go to Chery Wright record the cursor skips and goes to Ricky
De Souzas record.

In my Form Current() event i have
Private Sub Form_Current()
Me.btnnewcontact.Visible = (Me.ContactType = "Contacts Mktg")
End Sub

I have not tried this with changing the form layout. The new button once
diappeared does not appear again.

were am i going wrong.

really appreciate your time and patience.