Combos reseting ID

[Andy Roberts]

I have a client form (frmClient) and on that form is a tab control that has
two tabs (Jobs &Contacts) Each tab has a subform showing, in the case of
the jobs subform, all the jobs relating to the client displayed on the main
form.

On the Contacts subform I have all the contacts for each client (company) on
the main form. It has the following fields:

ContactID, Name, JobTitle, Mobile and a button at the end called Edit.

All the fields are locked and the user has to click the edit button which
opens a ContactEdit form where all the details for eachcontact can be
editted (there is much more info displayed on this form than in the subform
due to space). The JobTitle field is a cbo which is linked to a JobTitle
table and works fine except than when I select a job title from the drop
down it displays fine, however when I change the job title it replaces the
JobTitlefield in the JobTitle table with the primary key (ID of that
particular record. The same happens when I change the company a person
works for.

For example:

Joe Smith works for TestCompany and is a Supervisor. When I change his
company or his job title from the respective dropdown cbo it replaces
"TestCompany" witrh the ID of TestCompany and "Supervisor" with the ID of
supervisor in each respective table. Opening the tables proves the data is
being changed as oppose to just displaying it differently. I've obviously
got the same issue with both cbo's.

Any Thoughts?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

 

[Beetle]

If I'm reading your post correctly, it is doing exactly what it should. The
only data that should be in the Contacts table is the CompanyID and
JobTitleID, not the actual names. The names are known via the relationship.

 

[Andy Roberts]

Sean

Thanks for the response. I've not explained myself very well. All my
tables store the ID of the linked data - this isn't what's being changed.
Lets take the contact table which is linked to a JobTitle table via IDs. If
I change the jobtitle of a contact (I'm seeing the actual jobtitle via a
combo box), its actually changing the JobTitle text field.

My Job Title table starts out with ID and Job Title e..g (001,Supervisor)
and after using the forms ends up as (001,1)

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

 

[Klatuu]

Looks like you have fallen into the trap of the evil lookup table field.
When you have become pure of heart, you will forget they even exist.

 

[Andy Roberts]

Dave

Not sure I have. All the tables have straight forward fields - none of them
are look up fields (they were last week but I changed them all and all the
"look ups"are done using combo boxes on forms with ID fields hidden with a 0
width. Its the IDs being stored, just in this case the IDs are fine, its
just the txt field whichisbeing replaced by a number.

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

 

[John W. Vinson]

Not sure I have. All the tables have straight forward fields - none of them
are look up fields (they were last week but I changed them all and all the
"look ups"are done using combo boxes on forms with ID fields hidden with a 0
width. Its the IDs being stored, just in this case the IDs are fine, its
just the txt field whichisbeing replaced by a number.

The text value should exist in the lookup table, and ONLY in the lookup table.
are you expecting the text to be stored in the events table? It won't be, and
shouldn't be.

Perhaps you could post the SQL view of the Form's Recordsource, and of the
combo box (or boxes), and the Bound Column and Control Source properties of
the combo(s).

 

[Andy Roberts]

Hi John

I think I'm explaining this badly, so we'll give your approach a go.

I have a frmClient with a record source of tblClient. On this form I have a
a tab control with 2 pages. One of thepages contains a subform called
sfrmContacts which displays all the Reps that work for the client. This
sfrm is filtered by the ClientID of the main form and the record source is..

SELECT tblClientRep.ClientRepID, tblClientRep.ClientID,
tblClientRep.ClientRepTitle, tblClientRep.ClientRepFirstName,
tblClientRep.ClientRepLastName, tblClientRep.ClientRepMobile,
tblClientRep.ClientRepEmail, tblJobTitle.IndJobTitle, [ClientRepFirstName]+"
"+[ClientRepLastName] AS Name
FROM tblJobTitle INNER JOIN tblClientRep ON tblJobTitle.IndJobTitleID =
tblClientRep.ClientRepJobTitleID
ORDER BY tblClientRep.ClientRepLastName;

The subform is a continuous form and displays several fields from the above
query including ClientRepName etc. All these fields are locked and I have
an Edit button at the endof each row which when clicked opens a new form
called frmContacts which displays all the details for a Contact. The record
source for this form is a query called qryClientContacts...

SELECT tblClientRep.ClientRepID, tblClient.ClientName,
tblClientRep.ClientRepTitle, tblClientRep.ClientRepFirstName,
tblClientRep.ClientRepLastName, tblClientRep.ClientRepMobile,
tblClientRep.ClientRepEmail, tblJobTitle.IndJobTitle
FROM tblJobTitle RIGHT JOIN (tblClient INNER JOIN tblClientRep ON
tblClient.ClientID=tblClientRep.ClientID) ON
tblJobTitle.IndJobTitleID=tblClientRep.ClientRepJobTitleID
ORDER BY tblClientRep.ClientRepLastName DESC;

The form when opened is filtered using ClientRepID. 2 of the controls on
this form are cbos which show Client and Job Title. The cboClient lists all
the client companies from tblClient using...

SELECT tblClient.ClientID, tblClient.ClientName FROM tblClient ORDER BY
tblClient.ClientName;

....with the first column bound but set to 0 width so it can't be seen.

The cboJobTitle lists all the Job titles from tblJobTitle using...

SELECT tblJobTitle.IndJobTitleID, tblJobTitle.IndJobTitle FROM tblJobTitle
ORDER BY tblJobTitle.IndJobTitle;

....with the first column bound but set to 0 width so it can't be seen.

The problem exists in both these cbo. If I click the cboJobTitle and select
the first job title in the drop down it displays it in the cbo after
selection eg Janitor. If I then change this to a different job title, the
cbo then displays a number. This number is the same as the JobTitleID but
has replaced the selected value, so in this example Janitor would be
replaced by 2. The form is based on tblJobTitle so if I then opened this
table which contains 2 fields (an ID (primary key) and a text field
containing the job title) the Janior record, before using the cbo would have
been...

ID JobTitle
0002 Janitor

but after using the form reads...

ID JobTitle
0002 2

and all the ClientReps who were Janitors now display 2 on the form in the
cboJobTitle.

The same thing happens using the cboClient

I hope this explains a little clearer

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007

 

[Klatuu]

Since the values are being reversed in tblJobTitle, there is something
updating this table. Is the combo a bound control? What is happening in the
combo's after update? Is there any other code in your form that references
the job title combo?


--
Dave Hargis, Microsoft Access MVP

Hi John

I think I'm explaining this badly, so we'll give your approach a go.

I have a frmClient with a record source of tblClient. On this form I have a
a tab control with 2 pages. One of thepages contains a subform called
sfrmContacts which displays all the Reps that work for the client. This
sfrm is filtered by the ClientID of the main form and the record source is..

SELECT tblClientRep.ClientRepID, tblClientRep.ClientID,
tblClientRep.ClientRepTitle, tblClientRep.ClientRepFirstName,
tblClientRep.ClientRepLastName, tblClientRep.ClientRepMobile,
tblClientRep.ClientRepEmail, tblJobTitle.IndJobTitle, [ClientRepFirstName]+"
"+[ClientRepLastName] AS Name
FROM tblJobTitle INNER JOIN tblClientRep ON tblJobTitle.IndJobTitleID =
tblClientRep.ClientRepJobTitleID
ORDER BY tblClientRep.ClientRepLastName;

The subform is a continuous form and displays several fields from the above
query including ClientRepName etc. All these fields are locked and I have
an Edit button at the endof each row which when clicked opens a new form
called frmContacts which displays all the details for a Contact. The record
source for this form is a query called qryClientContacts...

SELECT tblClientRep.ClientRepID, tblClient.ClientName,
tblClientRep.ClientRepTitle, tblClientRep.ClientRepFirstName,
tblClientRep.ClientRepLastName, tblClientRep.ClientRepMobile,
tblClientRep.ClientRepEmail, tblJobTitle.IndJobTitle
FROM tblJobTitle RIGHT JOIN (tblClient INNER JOIN tblClientRep ON
tblClient.ClientID=tblClientRep.ClientID) ON
tblJobTitle.IndJobTitleID=tblClientRep.ClientRepJobTitleID
ORDER BY tblClientRep.ClientRepLastName DESC;

The form when opened is filtered using ClientRepID. 2 of the controls on
this form are cbos which show Client and Job Title. The cboClient lists all
the client companies from tblClient using...

SELECT tblClient.ClientID, tblClient.ClientName FROM tblClient ORDER BY
tblClient.ClientName;

....with the first column bound but set to 0 width so it can't be seen.

The cboJobTitle lists all the Job titles from tblJobTitle using...

SELECT tblJobTitle.IndJobTitleID, tblJobTitle.IndJobTitle FROM tblJobTitle
ORDER BY tblJobTitle.IndJobTitle;

....with the first column bound but set to 0 width so it can't be seen.

The problem exists in both these cbo. If I click the cboJobTitle and select
the first job title in the drop down it displays it in the cbo after
selection eg Janitor. If I then change this to a different job title, the
cbo then displays a number. This number is the same as the JobTitleID but
has replaced the selected value, so in this example Janitor would be
replaced by 2. The form is based on tblJobTitle so if I then opened this
table which contains 2 fields (an ID (primary key) and a text field
containing the job title) the Janior record, before using the cbo would have
been...

ID JobTitle
0002 Janitor

but after using the form reads...

ID JobTitle
0002 2

and all the ClientReps who were Janitors now display 2 on the form in the
cboJobTitle.

The same thing happens using the cboClient

I hope this explains a little clearer

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007