Changing control record source on save or open

[blobb]

I have a form based on a SQL query in the record source property. I am
having problems assigning the control record source.

Here is my SQL query:

SELECT DISTINCT D.PTid, T.T1_PTid, D.DateOfBirth, T.DOB, D.Gender, T.Gender,
D.Race, T.Race, D.Ethnicity, T.Ethnicity, D.Education, T.Education,
D.Handedness, T.Handedness, D.PFHxMD, T.FamilyHistoryOfMovementDisorder,
D.PFHxMDnum, T.NumberWithMD, D.PFHxPD, T.FamilyHistoryofParkinsonsDisease,
D.PFHxPDnum, T.NumberWithPD
FROM Demographics AS D RIGHT JOIN [Temporary] AS T ON D.PTid = T.T1_PTid
WHERE (((D.DemoID) Is Not Null));

Here are two of my controls (for illustration):

control 1: D.Gender
control 2: T.Gender

Everything looks fine on the design view but when I save the form or display
the form in form view the record source of control 2 changes from T.Gender to
D.Gender. I have reset the control record source many times but everytime I
review or save the form it resets back to D.Gender. Can anyone help me?

Thanks. blobb

 

[Douglas J. Steele]

I'm wondering whether the problem is that you're repeating field names. Try
using aliases:

SELECT DISTINCT D.PTid, T.T1_PTid, D.DateOfBirth, T.DOB, D.Gender AS
DGender,
T.Gender AS TGender, D.Race AS DRace, T.Race AS TRace, D.Ethnicity AS
DEthnicity,
T.Ethnicity AS TEthnicity, D.Education AS DEducation, T.Education AS
TEducation,
D.Handedness AS DHandedness, T.Handedness AS THandedness, D.PFHxMD,
T.FamilyHistoryOfMovementDisorder, D.PFHxMDnum, T.NumberWithMD, D.PFHxPD,
T.FamilyHistoryofParkinsonsDisease, D.PFHxPDnum, T.NumberWithPD
FROM Demographics AS D RIGHT JOIN [Temporary] AS T ON D.PTid = T.T1_PTid
WHERE (((D.DemoID) Is Not Null));

 

[Douglas J. Steele]

Forgot to mention, you'll have to change the control source for your
controls. For your example, you'd use

control 1: DGender
control 2: TGender


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm wondering whether the problem is that you're repeating field names.
Try using aliases:

SELECT DISTINCT D.PTid, T.T1_PTid, D.DateOfBirth, T.DOB, D.Gender AS
DGender,
T.Gender AS TGender, D.Race AS DRace, T.Race AS TRace, D.Ethnicity AS
DEthnicity,
T.Ethnicity AS TEthnicity, D.Education AS DEducation, T.Education AS
TEducation,
D.Handedness AS DHandedness, T.Handedness AS THandedness, D.PFHxMD,
T.FamilyHistoryOfMovementDisorder, D.PFHxMDnum, T.NumberWithMD, D.PFHxPD,
T.FamilyHistoryofParkinsonsDisease, D.PFHxPDnum, T.NumberWithPD
FROM Demographics AS D RIGHT JOIN [Temporary] AS T ON D.PTid = T.T1_PTid
WHERE (((D.DemoID) Is Not Null));


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a form based on a SQL query in the record source property. I am
having problems assigning the control record source.

Here is my SQL query:

SELECT DISTINCT D.PTid, T.T1_PTid, D.DateOfBirth, T.DOB, D.Gender,
T.Gender,
D.Race, T.Race, D.Ethnicity, T.Ethnicity, D.Education, T.Education,
D.Handedness, T.Handedness, D.PFHxMD, T.FamilyHistoryOfMovementDisorder,
D.PFHxMDnum, T.NumberWithMD, D.PFHxPD,
T.FamilyHistoryofParkinsonsDisease,
D.PFHxPDnum, T.NumberWithPD
FROM Demographics AS D RIGHT JOIN [Temporary] AS T ON D.PTid = T.T1_PTid
WHERE (((D.DemoID) Is Not Null));

Here are two of my controls (for illustration):

control 1: D.Gender
control 2: T.Gender

Everything looks fine on the design view but when I save the form or
display
the form in form view the record source of control 2 changes from
T.Gender to
D.Gender. I have reset the control record source many times but
everytime I
review or save the form it resets back to D.Gender. Can anyone help me?

Thanks. blobb

 

[blobb]

Thanks for your input. I finally fixed it by creating the sql query and then
creating a new form using the form wizard. I then was able to just paste in
by sql query in the form control source and everything worked just fine.
(Whereas before I did not use the form wizard).

blobb

Forgot to mention, you'll have to change the control source for your
controls. For your example, you'd use

control 1: DGender
control 2: TGender


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm wondering whether the problem is that you're repeating field names.
Try using aliases:

SELECT DISTINCT D.PTid, T.T1_PTid, D.DateOfBirth, T.DOB, D.Gender AS
DGender,
T.Gender AS TGender, D.Race AS DRace, T.Race AS TRace, D.Ethnicity AS
DEthnicity,
T.Ethnicity AS TEthnicity, D.Education AS DEducation, T.Education AS
TEducation,
D.Handedness AS DHandedness, T.Handedness AS THandedness, D.PFHxMD,
T.FamilyHistoryOfMovementDisorder, D.PFHxMDnum, T.NumberWithMD, D.PFHxPD,
T.FamilyHistoryofParkinsonsDisease, D.PFHxPDnum, T.NumberWithPD
FROM Demographics AS D RIGHT JOIN [Temporary] AS T ON D.PTid = T.T1_PTid
WHERE (((D.DemoID) Is Not Null));


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a form based on a SQL query in the record source property. I am
having problems assigning the control record source.

Here is my SQL query:

SELECT DISTINCT D.PTid, T.T1_PTid, D.DateOfBirth, T.DOB, D.Gender,
T.Gender,
D.Race, T.Race, D.Ethnicity, T.Ethnicity, D.Education, T.Education,
D.Handedness, T.Handedness, D.PFHxMD, T.FamilyHistoryOfMovementDisorder,
D.PFHxMDnum, T.NumberWithMD, D.PFHxPD,
T.FamilyHistoryofParkinsonsDisease,
D.PFHxPDnum, T.NumberWithPD
FROM Demographics AS D RIGHT JOIN [Temporary] AS T ON D.PTid = T.T1_PTid
WHERE (((D.DemoID) Is Not Null));

Here are two of my controls (for illustration):

control 1: D.Gender
control 2: T.Gender

Everything looks fine on the design view but when I save the form or
display
the form in form view the record source of control 2 changes from
T.Gender to
D.Gender. I have reset the control record source many times but
everytime I
review or save the form it resets back to D.Gender. Can anyone help me?

Thanks. blobb