Table relationships

[Guest]

I have two forms based on a single table for company projects. The first
form contains data regarding the projects; the second form contains multiple
"updates", i.e. a "date" field and a "comment" field where users can add an
"update" to the status/condition of the project. These are currently nicely
related so that when a user selects a project on the Project form and then
opens the Update form and enters a status update, it gets inserted into the
appropriate row on the table.

Problem is, I'm going to run out of room on the form and the table is
eventually going to get messy. What I'd like to do is present the users,
when they click the "Add/View Updates" button on the Project form, with a
single set of update fields (Date, Comment). I'm thinking I'll need to
separate my "Add/View Updates" button into two buttons, one for View and one
for Add. When the user clicks View I'd like them to be able to scroll to
view all comments for the selected project. When the user clicks Add, I'd
like them to be able to add a Date and Comment to the selected project.

To this end I've created a second "Comments" table...but I'm having trouble
getting the whole thing to work the way I mentioned above. Any suggestions
would be very much appreciated.

 

[Jeff Boyce]

Katie

Since you describe a one-to-many relationship (one project can have multiple
comments), you'll want a pair of tables (which seems to be what you've
described).

A common way to handle this in forms is to use a main form/subform
construction, where the subform control on the main form uses the (in your
case) projectID to link between project and comment. You are keeping
ProjectID (or whatever you are calling it) as a foreign key in the Comments
table, right?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

No, I don't have keys in either table yet (both have a "Project_ID" field); I
can see how to set a primary key...but how do I set a foreign key?

Also, my Project_ID field in the Project form is an auto-number field;
should it be the same in the Update form, or just a Number field?

 

[Douglas J. Steele]

You don't actually set foreign keys. Instead, you create a relationship
between the two tables, and Access takes care of the rest. (Make sure you've
defined the primary key in the parent table first).

 

[Jeff Boyce]

Katie

You set a primary key in design mode. The "key" button (or
right-click/select Primary Key) when you have the field highlighted will do
the trick.

You don't "set" a foreign key ... is just "is". In your child(many) table,
make sure you have a field that is of compatible data type (LongInt matches
Autonumber), and it helps if you use the same name. In your Comments table,
include a field named ProjectID, LongInt.

By the way, if you made the foreign key field an Autonumber, you'd be
telling Access to invent/create a number that is unique ... which means it
would have NO relationship to the Autonumber that Access creates in the
Project table!

Go to the relationships window and pull in both tables. Drag ProjectID
(your Primary Key in Projects) over onto ProjectID (a foreign key in
Comments). Access will prompt you for more clarification about the
relationship. Check Referential Integrity (but don't bother checking the
"cascade" checkboxes right now - you can always do it later, after reading
up on what they do in Access HELP).

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

Guys...thanks a bunch for the input! So far, so good...now I have to get the
functionality set up as mentioned in my original post. I'd like to try to
figure it out myself...but if you don't mind, I'll get back to you if I hit a
roadblock.

Thanks again,

Katie

 

[Jeff Boyce]

You're welcome. Thank you for taking a run at the next step. Who knows,
maybe you'll be back to answer the next question!

Consider posting back what the issue was and what you figured out -- folks
really benefit from seeing how someone else solve an issue they have...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Jeff Boyce]

Katie

See comments in-line below...

Believe it or not, I've just gotten back to this!

So...I now have a parent form, "Project", and a sub-form, "Updates", each
based on separate tables of the same name.

Whoa! "...same name..."? A main form based on the "Project" table and a
sub-form based on the "Updates" table would be a standard approach.

Each has a "Project_ID" field, of
type auto-number in the Project form, and long integer number in the Updates
form, and I've established the relationship between the two.

No, this is what I said would confuse Access and result in NO relationship.
An Autonumber field in a table is an independent, arbitrary, unique
identifier for rows IN THAT TABLE. If you relate two tables by their
Autonumbers, you've just guaranteed that there is NO relationship between
them. And by the way, the "field" is in the table, and the form has a
"control" that is bound to the underlying field. The "Project_ID" field in
"Project" could be Autonumber, but the related "Project_ID" field in
"Updates" should be a long integer.

In the Updates table I have a single field for "Date" and one for "Comment".
My question is, can these be added to the table dynamically, i.e. user adds
an update and a new set of columns is created in the table? Would an
"insert" statement do the trick? Further, can they then be displayed
dynamically?

The whole reason for determining the fields (i.e., columns) before starting
is so that you don't have to "dynamically... add" new fields. If you know
that comments will have a project to which they are related, then I'll infer
that your Updates tables will need:

UpdatesID (you could use an Autonumber here)
Project_ID (a long int, pointed back at Projects)
CommentDate (don't use "Date" - that's a reserved word in Access)
Comment

When you create two forms (one for Project, one for Updates), and put the
Updates form in the Project form (as a subform), you'll be prompted by
Access to show how they are connected. Show the Project_ID of Projects
connected to the Project_ID of Updates.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

Hi Jeff,

Thanks for your response. Sorry...I wasn't very clear in some of my
comments; I do, in fact, have a table "Projects" upon which the "Projects"
form is based, and a table "Updates" upon which the "Updates" form is based.
Each had a "Project_ID" field; in the Project table it is auto-number type,
and in the Updates table it is number, long integer. So, I think I'm okay in
that regard...and the functionality is what I was looking for.

However, I do have one small glitch. I originally had "Project Name" (a
field in the Project table/form) as a text box; users would navigate to the
desired project using the standard navigation bar at the bottom of the form.
I changed the "Project Name" field to a combo box to make it easier for users
to find a project. Problem is, when I open the form and click the dropdown
arrow in the Project Name combo box, I get an error message, which reads:
"The value you entered isn't valid for this field. For example, you may have
entered text in a numeric field or a number that is larger that the FieldSize
setting permits". I can't see anything wrong with the field type (text) or
the FieldSize setting (200). Any ideas?

Thanks,

Katie

 

[Guest]

Forgot to mention the most important part of the problem I outlined in my
earlier post...which is that after I get that message, the Project Name of
the first project in the table is replaced by the last Project_ID number,
i.e. the project "CSUM" becomes "130"...weird.

 

[Jeff Boyce]

Katie

Based on your description, I would look at the (new) Project combo box on
your form. I suspect you (still) have it bound to a field in the underlying
source. To use the combo box to look up a project, consider making it an
unbound control.

Also, if there's "confusion" between the ID# and the text, is your
underlying field defined as a "lookup" data type?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

Hi Jeff,

I unbound the combo box, and, yes, the underlying field is a lookup.

I no longer get the message, but when the form opens, displaying the first
project, all the data is displayed except that the "Project Name" is blank,
and remains blank when navigating through the forms (using "Next" button).

 

[Jeff Boyce]

Katie

One way to get a "Project Name" control to display is to use the AfterUpdate
event of the combo box. Your code might look something like:

Me!txtProjectName = Me!cboSelectProject.Column(n)

where "n" is the n-1 column in your query (i.e., the one returning Project
Name). Remember that .Column() is zero-based.

I suggest that you not use "lookup" data type fields in your tables.
Convert the "combo box" setting on the Lookup tab to "text".

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

Hi Jeff,

I think I did what you suggested...but to clarify, here's what I currently
have:
In the Projects combobox properties, there are no entries for Control
Source, Row Source Type, or Row Source
I changed the Project column of the underlying table to "Text Box" in
the Lookup tab.

Also, FYI, here is the current code for the "After Update" event -- I wasn't
sure where to insert the code you suggested...

Private Sub Combo48_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Project_ID] = " & Str(Nz(Me![Combo48], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me!txtProjectName = Me!cboSelectProject.Column(2)
End Sub

At this point, in addition to the earlier problems, the Projects combo box
no longer has a list of projects.

HELP!

 

[Guest]

Hold everything...I've been experimenting (oh-oh!)...and have made some
improvements. I left the underlying field properties (i.e. lookup tab) as
mentioned below, but did re-enter the source in the combo box properties,
thus:
Control Source: ProjectName
Row Source Type: Table/Query
Row Source: Projects (db name)
Bound Column: 2

Also, I realized I needed to make other changes to your event code, so it
now reads:
Me!txtProjectName = Me!cboCombo48.Column(2)

Things are better -- when the form opens the name of the first project
appears, and if I scroll through the projects using the "Next" button, all is
well. However, the dropdown list contains numbers from the "Project_Number"
column, which is the number 1 column, "Project_ID" being 0.

I've tried all kinds of combinations, but can't seem to capture the list of
projects.

Hi Jeff,

I think I did what you suggested...but to clarify, here's what I currently
have:
In the Projects combobox properties, there are no entries for Control
Source, Row Source Type, or Row Source
I changed the Project column of the underlying table to "Text Box" in
the Lookup tab.

Also, FYI, here is the current code for the "After Update" event -- I wasn't
sure where to insert the code you suggested...

Private Sub Combo48_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Project_ID] = " & Str(Nz(Me![Combo48], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me!txtProjectName = Me!cboSelectProject.Column(2)
End Sub

At this point, in addition to the earlier problems, the Projects combo box
no longer has a list of projects.

HELP!

Katie

One way to get a "Project Name" control to display is to use the AfterUpdate
event of the combo box. Your code might look something like:

Me!txtProjectName = Me!cboSelectProject.Column(n)

where "n" is the n-1 column in your query (i.e., the one returning Project
Name). Remember that .Column() is zero-based.

I suggest that you not use "lookup" data type fields in your tables.
Convert the "combo box" setting on the Lookup tab to "text".

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Jeff Boyce]

Katie

Open the form in design mode. Highlight the combo box. Select Properties.

Find the property dealing with Column Widths. Set the first (?your ID)
column to width = 0. Now it won't show.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Hold everything...I've been experimenting (oh-oh!)...and have made some
improvements. I left the underlying field properties (i.e. lookup tab) as
mentioned below, but did re-enter the source in the combo box properties,
thus:
Control Source: ProjectName
Row Source Type: Table/Query
Row Source: Projects (db name)
Bound Column: 2

Also, I realized I needed to make other changes to your event code, so it
now reads:
Me!txtProjectName = Me!cboCombo48.Column(2)

Things are better -- when the form opens the name of the first project
appears, and if I scroll through the projects using the "Next" button, all is
well. However, the dropdown list contains numbers from the "Project_Number"
column, which is the number 1 column, "Project_ID" being 0.

I've tried all kinds of combinations, but can't seem to capture the list of
projects.

Hi Jeff,

I think I did what you suggested...but to clarify, here's what I currently
have:
In the Projects combobox properties, there are no entries for Control
Source, Row Source Type, or Row Source
I changed the Project column of the underlying table to "Text Box" in
the Lookup tab.

Also, FYI, here is the current code for the "After Update" event -- I wasn't
sure where to insert the code you suggested...

Private Sub Combo48_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Project_ID] = " & Str(Nz(Me![Combo48], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me!txtProjectName = Me!cboSelectProject.Column(2)
End Sub

At this point, in addition to the earlier problems, the Projects combo box
no longer has a list of projects.

HELP!

Katie

One way to get a "Project Name" control to display is to use the AfterUpdate
event of the combo box. Your code might look something like:

Me!txtProjectName = Me!cboSelectProject.Column(n)

where "n" is the n-1 column in your query (i.e., the one returning Project
Name). Remember that .Column() is zero-based.

I suggest that you not use "lookup" data type fields in your tables.
Convert the "combo box" setting on the Lookup tab to "text".

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


Hi Jeff,

I unbound the combo box, and, yes, the underlying field is a lookup.

I no longer get the message, but when the form opens, displaying the first
project, all the data is displayed except that the "Project Name" is
blank,
and remains blank when navigating through the forms (using "Next" button).

 

[Guest]

Katie

Open the form in design mode. Highlight the combo box. Select Properties.

Find the property dealing with Column Widths. Set the first (?your ID)
column to width = 0. Now it won't show.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Hold everything...I've been experimenting (oh-oh!)...and have made some
improvements. I left the underlying field properties (i.e. lookup tab) as
mentioned below, but did re-enter the source in the combo box properties,
thus:
Control Source: ProjectName
Row Source Type: Table/Query
Row Source: Projects (db name)
Bound Column: 2

Also, I realized I needed to make other changes to your event code, so it
now reads:
Me!txtProjectName = Me!cboCombo48.Column(2)

Things are better -- when the form opens the name of the first project
appears, and if I scroll through the projects using the "Next" button, all is
well. However, the dropdown list contains numbers from the "Project_Number"
column, which is the number 1 column, "Project_ID" being 0.

I've tried all kinds of combinations, but can't seem to capture the list of
projects.

Hi Jeff,

I think I did what you suggested...but to clarify, here's what I currently
have:
In the Projects combobox properties, there are no entries for Control
Source, Row Source Type, or Row Source
I changed the Project column of the underlying table to "Text Box" in
the Lookup tab.

Also, FYI, here is the current code for the "After Update" event -- I wasn't
sure where to insert the code you suggested...

Private Sub Combo48_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Project_ID] = " & Str(Nz(Me![Combo48], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me!txtProjectName = Me!cboSelectProject.Column(2)
End Sub

At this point, in addition to the earlier problems, the Projects combo box
no longer has a list of projects.

HELP!


:

Katie

One way to get a "Project Name" control to display is to use the AfterUpdate
event of the combo box. Your code might look something like:

Me!txtProjectName = Me!cboSelectProject.Column(n)

where "n" is the n-1 column in your query (i.e., the one returning Project
Name). Remember that .Column() is zero-based.

I suggest that you not use "lookup" data type fields in your tables.
Convert the "combo box" setting on the Lookup tab to "text".

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


Hi Jeff,

I unbound the combo box, and, yes, the underlying field is a lookup.

I no longer get the message, but when the form opens, displaying the first
project, all the data is displayed except that the "Project Name" is
blank,
and remains blank when navigating through the forms (using "Next" button).

 

[Jeff Boyce]

Sorry, I don't see a response in this thread.

Jeff

Katie

Open the form in design mode. Highlight the combo box. Select Properties.

Find the property dealing with Column Widths. Set the first (?your ID)
column to width = 0. Now it won't show.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Hold everything...I've been experimenting (oh-oh!)...and have made some
improvements. I left the underlying field properties (i.e. lookup tab) as
mentioned below, but did re-enter the source in the combo box properties,
thus:
Control Source: ProjectName
Row Source Type: Table/Query
Row Source: Projects (db name)
Bound Column: 2

Also, I realized I needed to make other changes to your event code, so it
now reads:
Me!txtProjectName = Me!cboCombo48.Column(2)

Things are better -- when the form opens the name of the first project
appears, and if I scroll through the projects using the "Next" button,

all
is

well. However, the dropdown list contains numbers from the "Project_Number"
column, which is the number 1 column, "Project_ID" being 0.

I've tried all kinds of combinations, but can't seem to capture the

list
of

projects.


:

Hi Jeff,

I think I did what you suggested...but to clarify, here's what I currently
have:
In the Projects combobox properties, there are no entries for Control
Source, Row Source Type, or Row Source
I changed the Project column of the underlying table to "Text

Box"
in

the Lookup tab.

Also, FYI, here is the current code for the "After Update" event --

I
wasn't

sure where to insert the code you suggested...

Private Sub Combo48_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Project_ID] = " & Str(Nz(Me![Combo48], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me!txtProjectName = Me!cboSelectProject.Column(2)
End Sub

At this point, in addition to the earlier problems, the Projects

combo
box

no longer has a list of projects.

HELP!


:

Katie

One way to get a "Project Name" control to display is to use the AfterUpdate
event of the combo box. Your code might look something like:

Me!txtProjectName = Me!cboSelectProject.Column(n)

where "n" is the n-1 column in your query (i.e., the one returning Project
Name). Remember that .Column() is zero-based.

I suggest that you not use "lookup" data type fields in your tables.
Convert the "combo box" setting on the Lookup tab to "text".

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


Hi Jeff,

I unbound the combo box, and, yes, the underlying field is a lookup.

I no longer get the message, but when the form opens, displaying

the
first

project, all the data is displayed except that the "Project Name" is
blank,
and remains blank when navigating through the forms (using

"Next"
button).

 

[Guest]

Sorry...lost it somehow.

Anyway, your last suggestion didn't work...so I think I'm giving up. I had
wanted users to be able to select a project to view and/or update instead of
having to navigate through all the projects. But, there are only about 4
users, updates won't be all that frequent, and there are only about 30
projects. And I've reached the point of diminishing returns!

I'm sure there's a way to do it, but I know it's very difficult for you to
"see" exactly what the problem is from a distance.

In any event, thanks for all your help. I learned a lot, even if the result
isn't quite what I wanted.

Sorry, I don't see a response in this thread.

Jeff

Katie

Open the form in design mode. Highlight the combo box. Select Properties.

Find the property dealing with Column Widths. Set the first (?your ID)
column to width = 0. Now it won't show.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


Hold everything...I've been experimenting (oh-oh!)...and have made some
improvements. I left the underlying field properties (i.e. lookup tab) as
mentioned below, but did re-enter the source in the combo box properties,
thus:
Control Source: ProjectName
Row Source Type: Table/Query
Row Source: Projects (db name)
Bound Column: 2

Also, I realized I needed to make other changes to your event code, so it
now reads:
Me!txtProjectName = Me!cboCombo48.Column(2)

Things are better -- when the form opens the name of the first project
appears, and if I scroll through the projects using the "Next" button, all
is
well. However, the dropdown list contains numbers from the
"Project_Number"
column, which is the number 1 column, "Project_ID" being 0.

I've tried all kinds of combinations, but can't seem to capture the list
of
projects.


:

Hi Jeff,

I think I did what you suggested...but to clarify, here's what I
currently
have:
In the Projects combobox properties, there are no entries for
Control
Source, Row Source Type, or Row Source
I changed the Project column of the underlying table to "Text Box"
in
the Lookup tab.

Also, FYI, here is the current code for the "After Update" event -- I
wasn't
sure where to insert the code you suggested...

Private Sub Combo48_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Project_ID] = " & Str(Nz(Me![Combo48], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me!txtProjectName = Me!cboSelectProject.Column(2)
End Sub

At this point, in addition to the earlier problems, the Projects combo
box
no longer has a list of projects.

HELP!


:

Katie

One way to get a "Project Name" control to display is to use the
AfterUpdate
event of the combo box. Your code might look something like:

Me!txtProjectName = Me!cboSelectProject.Column(n)

where "n" is the n-1 column in your query (i.e., the one returning
Project
Name). Remember that .Column() is zero-based.

I suggest that you not use "lookup" data type fields in your tables.
Convert the "combo box" setting on the Lookup tab to "text".

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


Hi Jeff,

I unbound the combo box, and, yes, the underlying field is a lookup.

I no longer get the message, but when the form opens, displaying the
first
project, all the data is displayed except that the "Project Name" is
blank,
and remains blank when navigating through the forms (using "Next"
button).

 

[Jeff Boyce]

Katie

Thanks for sticking with it ... and I understand "diminishing returns"!

If the value in having this issue resolved is high enough for you, you could
consider hiring the work done, or hiring a "mentor". There may also be
college students in your area who would take on the challenge for love or
classwork...

Good luck!

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Sorry...lost it somehow.

Anyway, your last suggestion didn't work...so I think I'm giving up. I had
wanted users to be able to select a project to view and/or update instead of
having to navigate through all the projects. But, there are only about 4
users, updates won't be all that frequent, and there are only about 30
projects. And I've reached the point of diminishing returns!

I'm sure there's a way to do it, but I know it's very difficult for you to
"see" exactly what the problem is from a distance.

In any event, thanks for all your help. I learned a lot, even if the result
isn't quite what I wanted.

Sorry, I don't see a response in this thread.

Jeff

:

Katie

Open the form in design mode. Highlight the combo box. Select Properties.

Find the property dealing with Column Widths. Set the first (?your ID)
column to width = 0. Now it won't show.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


Hold everything...I've been experimenting (oh-oh!)...and have made some
improvements. I left the underlying field properties (i.e. lookup tab) as
mentioned below, but did re-enter the source in the combo box properties,
thus:
Control Source: ProjectName
Row Source Type: Table/Query
Row Source: Projects (db name)
Bound Column: 2

Also, I realized I needed to make other changes to your event

code, so
it

now reads:
Me!txtProjectName = Me!cboCombo48.Column(2)

Things are better -- when the form opens the name of the first project
appears, and if I scroll through the projects using the "Next"

button,
all

is
well. However, the dropdown list contains numbers from the
"Project_Number"
column, which is the number 1 column, "Project_ID" being 0.

I've tried all kinds of combinations, but can't seem to capture

the
list

of
projects.


:

Hi Jeff,

I think I did what you suggested...but to clarify, here's what I
currently
have:
In the Projects combobox properties, there are no entries for
Control
Source, Row Source Type, or Row Source
I changed the Project column of the underlying table to

"Text
Box"

in
the Lookup tab.

Also, FYI, here is the current code for the "After Update"

event --
I

wasn't
sure where to insert the code you suggested...

Private Sub Combo48_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Project_ID] = " & Str(Nz(Me![Combo48], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me!txtProjectName = Me!cboSelectProject.Column(2)
End Sub

At this point, in addition to the earlier problems, the Projects combo
box
no longer has a list of projects.

HELP!


:

Katie

One way to get a "Project Name" control to display is to use the
AfterUpdate
event of the combo box. Your code might look something like:

Me!txtProjectName = Me!cboSelectProject.Column(n)

where "n" is the n-1 column in your query (i.e., the one returning
Project
Name). Remember that .Column() is zero-based.

I suggest that you not use "lookup" data type fields in your tables.
Convert the "combo box" setting on the Lookup tab to "text".

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


Hi Jeff,

I unbound the combo box, and, yes, the underlying field is a lookup.

I no longer get the message, but when the form opens,

displaying
the

first
project, all the data is displayed except that the "Project Name" is
blank,
and remains blank when navigating through the forms (using "Next"
button).