Method to display specific records in control boxes on a form?

[WildlyHarry]

I have a form where I want to display specific fields from a query on one
continuious form. For example, I want to display the record in row 1, column
1 of my query in text box 1, and the records from row 2, column 1 in text box
2, and so on in that manner. I am placing VBA behind the text boxes,
specific to the values in those boxes, therefore a dropdown and a subform
will not work. I am currently using the dlookup method and it returns the
record from the first row of the query. Is there anyway to specify the
dlookup to return the value in the second row? Or is there some other method
that I should consider? Thanks in advance.

 

[Jeff Boyce]

I'm trying to envision a situation in which you'd take data from one row to
display in one control/textbox, but data from another to display in a
second, and ... What happens when you run out of textbox controls because
the number of rows is greater than the number of textboxes?

"How" depends on "what", and I don't have a clear picture of what your data
looks like or the table structure.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[WildlyHarry]

What I am building is a project repository for my department. The project
records have a ton detail associated with them, literally dozens of fields.
What I want to happen is to place the name of the projects associated with
selected individuals into the different text boxes. So that the individual
can see all of thier projects, at most 5-10, and then click on the text box.
And be directed to a form specific to the project name where then can make
changes to the project record. I already have the code set behind the first
text box and everything works fine. All I need is to be able to display
project name 2, 3, 4, etc in the other text boxes.

I know it sounds confusing, but in a nutshell. I want a form to display all
project names. When a user clicks on a project name, a form opens for that
project for data entry. Thoughts?

 

[Hans Up]

What I am building is a project repository for my department. The project
records have a ton detail associated with them, literally dozens of fields.
What I want to happen is to place the name of the projects associated with
selected individuals into the different text boxes. So that the individual
can see all of thier projects, at most 5-10, and then click on the text box.
And be directed to a form specific to the project name where then can make
changes to the project record. I already have the code set behind the first
text box and everything works fine. All I need is to be able to display
project name 2, 3, 4, etc in the other text boxes.

I know it sounds confusing, but in a nutshell. I want a form to display all
project names. When a user clicks on a project name, a form opens for that
project for data entry. Thoughts?

You could use a continuous form to present the project records
associated with the selected individual. The style of the form would
not match the layout you described, but it's simple and easy to build a
continuous form in Access.

 

[WildlyHarry]

Thanks for the the suggestion, but my users are not incredibly savvy. If
they do not see all of thier projects displayed right away, they will assume
that the first project is thier only project. They have to see everything
first and then move forward.

 

[Hans Up]

Thanks for the the suggestion, but my users are not incredibly savvy. If
they do not see all of thier projects displayed right away, they will assume
that the first project is thier only project. They have to see everything
first and then move forward.

With "at most 5-10" projects per user, you should be able to display
them all at the same time in a continuous form.

 

[WildlyHarry]

The continuos form will not work. I did find a way around my issue though.
I wrote 5 different queries. The first query had no limits. I used dlookup
on the first query to display the project name in the first text box. The
second query is idnentical but I limited it to records not equal to the first
text box. Then I used dlookup on the second query to return value for the
second text box. The third query is set not equal to the values in text box
1 and 2. And so on. Thanks for the help.

 

[Jeff Boyce]

One approach would be to use a main form on which you select an individual,
a list box that displays summary info for the selected individual's
projects, and a subform that displays the particulars when the user selects
a single project from that listbox.

Regards

Jeff Boyce
Microsoft Office/Access MVP