On update, move new data to next empty location

[Mike DFR]

Access 2002/3
One combo box, the user types in data which then appears in a text box.
This needs to be saved for later use, there can be up to 52 entries.
The problem is that each time the data changes, the result must move to the
next location.
(this can be text boxes or a new table or exported)
How can I make a list from the single point data entry, save to a number of
locations.

 

[Rick Brandt]

Access 2002/3
One combo box, the user types in data which then appears in a text
box. This needs to be saved for later use, there can be up to 52
entries.
The problem is that each time the data changes, the result must move
to the next location.
(this can be text boxes or a new table or exported)
How can I make a list from the single point data entry, save to a
number of locations.

Is there a reason you don't have the user simply type in the TextBoxes? You
could make them all ComboBoxes if you want the features that a ComboBox
provides.

 

[Mike DFR]

I need to use combo boxes because the entry verifiries the data in a recordset.
I was trying not to complicate the question.
In brief, the user is filling an order form. The data can be found by either
part number or description, (one is used to verify the other). There are over
8,000 parts hence the need for verification. The order form, (Excel), has
space for 52 entries.
If I used 52 combo boxes, I would need 52 text boxes, then anoth 52 to send
the data to Excel. I think this would make the program too large.
I am surprised at not being able to find the answer to what seems like a
simple question, "Find the data, place it in row one, find the next data,
place it in row two, or text box two or whatever, continue up to 52".
The user finds the data in one place and the order form is populated
automatically.
Thanks for you assistance so far.

 

[Rick Brandt]

I need to use combo boxes because the entry verifiries the data in a
recordset. I was trying not to complicate the question.
In brief, the user is filling an order form. The data can be found by
either part number or description, (one is used to verify the other).
There are over 8,000 parts hence the need for verification. The order
form, (Excel), has space for 52 entries.
If I used 52 combo boxes, I would need 52 text boxes, then anoth 52
to send the data to Excel. I think this would make the program too
large.
I am surprised at not being able to find the answer to what seems
like a simple question, "Find the data, place it in row one, find the
next data, place it in row two, or text box two or whatever, continue
up to 52".
The user finds the data in one place and the order form is populated
automatically.
Thanks for you assistance so far.

So you are doing this in Excel? Sorry, but I can't be much help for that.
In a database you would have 52 *records* in a continuous form each entered
with a ComboBox and no need for a companion TextBox at all. I have no idea
how Excel forms work. You should probably ask the question in an Excel
group.

 

[Mike DFR]

So you are doing this in Excel? Sorry, but I can't be much help for that.
In a database you would have 52 *records* in a continuous form each entered
with a ComboBox and no need for a companion TextBox at all. I have no idea
how Excel forms work. You should probably ask the question in an Excel
group.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com


No, I am doing this in Acess then transfering the data to an excel spreadsheet, as the company requires the orders to be in this format.

All I need to do is select a part in a combo box, (recordsetclone), the
result appears in a text box and the combo box clears.
I need the first record found to be saved as part one, the next to be saved
as part two, etc. once all the parts have been found and saved in a list, I
can then transfer the block of data.
At present, when the second part is selected it overwrites the first part in
the same location.
I just need a command button that will go to the next empty space after
update.
(& next, goto next, move save next, or something) I just do not know how to
put the code together.
Someone has suggested transferspreadsheet, but I still need to know how fill
the table before I transfer.

 

[Rick Brandt]

All I need to do is select a part in a combo box, (recordsetclone),
the result appears in a text box and the combo box clears.
I need the first record found to be saved as part one, the next to be
saved as part two, etc. once all the parts have been found and saved
in a list, I can then transfer the block of data.
At present, when the second part is selected it overwrites the first
part in the same location.
I just need a command button that will go to the next empty space
after update.
(& next, goto next, move save next, or something) I just do not know
how to put the code together.
Someone has suggested transferspreadsheet, but I still need to know
how fill the table before I transfer.

You are really going about this the hard way. You can create a table in Access
that has the same (or similar) structure as the Excel file that you want to
export to. Then build a continuous view form bound to the table that uses a
ComboBox for entry/ Make as many rows as you need with the form and then export
the table to Excel.

This would require zero code.

 

[Mike DFR]

You are really going about this the hard way. You can create a table in Access
that has the same (or similar) structure as the Excel file that you want to
export to. Then build a continuous view form bound to the table that uses a
ComboBox for entry/ Make as many rows as you need with the form and then export
the table to Excel.

This would require zero code.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

I'm sorry Rick, I am totally lost now.

I created a continuous form as you suggested, but this list all the parts in
the table.
It is the table in form view.
I only want to list the parts I select on the main form.
I have tried using the add record command, but this just adds records to the
existing table.
How to do this using zero code has confused me even more.
If it helps to explain the problem, when you purchase on the internet you
select the goods then click a button "add to cart" which creates a list of
the things you select.
This is what I am trying to achieve.
'find your part, add to list', then the list can be printed, saved or
whatever.
Thanks for your help and patience so far.

 

[Rick Brandt]

I'm sorry Rick, I am totally lost now.
I created a continuous form as you suggested, but this list all the
parts in the table.
It is the table in form view.

Okay it sounds like you based your form on the table that you want to CHOOSE
parts from. That table should only be used to supply the drop-down list for the
ComboBox.

You need a NEW table that your form is bound to in which you will create records
with the form and then the new table is the one you export to Excel.

I only want to list the parts I select on the main form.
I have tried using the add record command, but this just adds records
to the existing table.
How to do this using zero code has confused me even more.
If it helps to explain the problem, when you purchase on the internet
you select the goods then click a button "add to cart" which creates
a list of the things you select.
This is what I am trying to achieve.

Exactly. That web application would have a list of things that "can be
purchased" off in some big lookup table and another table is being populated
with the list of things that you have "actually purchased". Currently you have
the first table, but do not have the second.

Actually a typical "Order Entry" system would be more involved than this and
have more tables. Normally, there would be a main table with a single record
"per order" and another table that contains multiple records for the items on
each order. These tables would have a common key field or fields that allow you
to relate the items ordered to the specific main order record. Then various
lookup tables would provide selection lists for Customer, Items, etc..

 

[Mike DFR]

Thanks for your help
It looks like I was going up the wrong path.
I will redesign and come back with the result through this thread.
(It may take a while though)
Thanks again for your guidance.