Re: #Name? displayed in Text Box

Hi Crystal

Sorry about the delay in getting back.

I've digested your combo-box approach and I'll be interested to have a play
with it.

Tom Wickerath came up with the solution to my problem - the form was linked
with code to a subform (data-sheet view) and the problem fields were not in
the SQL for the subform RecordSource.

Thanks


--
Graham


"strive4peace" wrote:

hidden combobox trick
---

Hi Graham,

"practicality overrides theory"

I can relate to that

is this form used for input or just display?

One thing you have is a lot of LEFT JOINs... cannot you display
corresponding Names using the ole hidden combobox trick?

combobox:
Name --> ID_field
ControlSource --> ID_field
RowSource -->
SELECT ID_field, SomeName, field3
FROM Tablename
ORDER BY SomeName
Visible --> No (or Yes -- depends what you need)

now, you can make a calculated control for SomeName

controlSource --> =ID_field.column(1)

why do you have 2 age groups on this record instead of using a subform?


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Graham wrote:
Thanks Crystal

I accept your comment about normalization.

While my experience with Access is limited, I am an experienced A/P who
prides himself on normalized design except that there are times when
practicality overrides theory.

Enough of the sermon - I realleally appreciate everyones help on this forum.

The way this application needs to function, I can't see how to achieve
results without having the similarly named fields with numeric
differentiation.

The SQL for RecordSource is
SELECT tEnrolment.*, [tEnrolment.ParticipantFName] & " " &
[tEnrolment.ParticipantLName] AS Participant, [tMstrProgramName.Description]
& " : " & [StartDate] AS Program, tMstrCultural.Description AS Cultural,
tMstrMaritalStatus.Description AS Marital, tMstrStaff.FirstName & " " &
tMstrStaff.LastName AS Staff, tMstrProgram.AgeGrp1ID, tMstrProgram.AgeGrp2ID
FROM (((tEnrolment LEFT JOIN (tMstrProgram LEFT JOIN tMstrProgramName ON
tMstrProgram.ProgNameID = tMstrProgramName.ProgNameID) ON
tEnrolment.ProgramID = tMstrProgram.ProgramID) LEFT JOIN tMstrCultural ON
tEnrolment.CulturalID = tMstrCultural.CulturalID) LEFT JOIN
tMstrMaritalStatus ON tEnrolment.MaritalStatusID =
tMstrMaritalStatus.MaritalStatusID) LEFT JOIN tMstrStaff ON
tEnrolment.StaffID = tMstrStaff.StaffID;


.

Relevant Pages

  • Re: setting RecordSource vs. setting RecordSource and Filter
    ... the subforms to display nothing since the link master ... My top-level form has a subform, and a sub-subform, etc. Could I leave my ... subforms bound and then just hook up the RecordSource of my top-level form ... it loads the form without any filter, ...
    (microsoft.public.access.modulesdaovba)
  • Re: Unbound forms recordsource property changes
    ... Dim prm As ADODB.Parameter ... The form has several tabs and as a tab is selected I use the on change event ... to call the appropriate sub to populate the subform on the tab with data ... I then set the recordsource of the form via vba to the ...
    (comp.databases.ms-access)
  • Re: Access 2003 Forms and Subforms with Parameter Query
    ... If RecordSource = "" Then ... <MS ACCESS MVP> ... subform to open the parameter query (or to pass along the recordset ... the "onclick" event for the button to run the query ...
    (microsoft.public.access.formscoding)
  • Re: How to Apply Current Recordsource?
    ... If it is in a subform then screen.ActiveControl.Parent.Name is not ... As other forms open the Recordsource will be a different query. ... If I were to place the toolbar on ... There is a better way to filter: ...
    (microsoft.public.access.modulesdaovba)
  • Re: Access 2003 Forms and Subforms with Parameter Query
    ... "Ken Snell (MVP)" wrote: ... I don't have code for the subform now. ... then assign that query to the subform's RecordSource. ...
    (microsoft.public.access.formscoding)