|
|
I have a main table, tblParents, and a one-to-many related table,
tblParentContacts, related by ParentID, an autonumber.
I am trying to design a form in datasheet mode based on a query.
The query contains ParentID, Last Name, FirstName, and one field that
concatenates the parent's first and last names (LastName, FirstName) from
tblParents and also contains the ContactDate, ContactTime, ContactReason,
etc. from tblParentContacts. (I've also tried using ParentID from
tblParentContact instead - no difference.)
In the first column of the datasheet form I want a combo box that will list
all the names in tblParents in the LastName, FirstName format. The person
entering the data will select a parent from that combo box and then enter the
details of the contact. I don't want the form to include the ParentID since
we don't use that outside of the database.
However, my combo box is always blank and nothing can even be entered in
that field. I'm assuming the query isn't updateable with that calculated
field, but I don't know how else to accomplish this.
Thanks in advance for your help.
|
|
You should not be trying to concantenate the name in the query
that is used as the recordsource for your form. You should do the
concantenation in the Row Source query of the combo box.
In other words, the form's record source should just have ParentID,
ContactDate, Contactreason, etc. from tblParentContacts. Your combo
box would then have properties like;
Control Source: ParentID (from the form's record source)
Row Source: Select ParentID, LastName & ", " & FirstName As LastFirst
From tblParents Order By LastName
Bound Column: 1
Column Count: 2
Column Widths: 0", 2" (or whatever width works best)
On a side note, I see no reason to store the ContactDate and ContactTime
in two separate fields. You can store both in one Date/Time field.
--
_________
Sean Bailey
"J. Renee" wrote:
[Quoted Text] > I have a main table, tblParents, and a one-to-many related table,
> tblParentContacts, related by ParentID, an autonumber.
>
> I am trying to design a form in datasheet mode based on a query.
>
> The query contains ParentID, Last Name, FirstName, and one field that
> concatenates the parent's first and last names (LastName, FirstName) from
> tblParents and also contains the ContactDate, ContactTime, ContactReason,
> etc. from tblParentContacts. (I've also tried using ParentID from
> tblParentContact instead - no difference.)
>
> In the first column of the datasheet form I want a combo box that will list
> all the names in tblParents in the LastName, FirstName format. The person
> entering the data will select a parent from that combo box and then enter the
> details of the contact. I don't want the form to include the ParentID since
> we don't use that outside of the database.
>
> However, my combo box is always blank and nothing can even be entered in
> that field. I'm assuming the query isn't updateable with that calculated
> field, but I don't know how else to accomplish this.
>
> Thanks in advance for your help.
>
|
|
Thank you so much. That was perfect!
Good point about the ContactTime field, but in this case it's just the
number of minutes spent with the Parent. The exact time of day isn't
important.
"Beetle" wrote:
[Quoted Text] > You should not be trying to concantenate the name in the query
> that is used as the recordsource for your form. You should do the
> concantenation in the Row Source query of the combo box.
>
> In other words, the form's record source should just have ParentID,
> ContactDate, Contactreason, etc. from tblParentContacts. Your combo
> box would then have properties like;
>
> Control Source: ParentID (from the form's record source)
> Row Source: Select ParentID, LastName & ", " & FirstName As LastFirst
> From tblParents Order By LastName
> Bound Column: 1
> Column Count: 2
> Column Widths: 0", 2" (or whatever width works best)
>
>
> On a side note, I see no reason to store the ContactDate and ContactTime
> in two separate fields. You can store both in one Date/Time field.
>
> --
> _________
>
> Sean Bailey
>
>
> "J. Renee" wrote:
>
> > I have a main table, tblParents, and a one-to-many related table,
> > tblParentContacts, related by ParentID, an autonumber.
> >
> > I am trying to design a form in datasheet mode based on a query.
> >
> > The query contains ParentID, Last Name, FirstName, and one field that
> > concatenates the parent's first and last names (LastName, FirstName) from
> > tblParents and also contains the ContactDate, ContactTime, ContactReason,
> > etc. from tblParentContacts. (I've also tried using ParentID from
> > tblParentContact instead - no difference.)
> >
> > In the first column of the datasheet form I want a combo box that will list
> > all the names in tblParents in the LastName, FirstName format. The person
> > entering the data will select a parent from that combo box and then enter the
> > details of the contact. I don't want the form to include the ParentID since
> > we don't use that outside of the database.
> >
> > However, my combo box is always blank and nothing can even be entered in
> > that field. I'm assuming the query isn't updateable with that calculated
> > field, but I don't know how else to accomplish this.
> >
> > Thanks in advance for your help.
> >
|
|
|