incorporate a combo box and text box within same listbox

Can someone point me in the right direction to accomplish the following.. I
want to add a subform (I think this is what i need??) to another existing
form. The subform needs to include a listbox (only to include 1 combo box
and 1 text box). The combo box (account #) values will come from a table
field and the text box is an amount ($$) field.. that the user enters in.
The user may have as many records (accts/amts) as necessary. After the first
combo box value is selected i would like the next line/record to appear
(cursor to go to the next line and where the user can enter another
account/amt).. I've been getting no where with this.. anyone have advice?
thanks.

SORRY FOR THE DOUBLE-POSTING.. I KEPT RECEIVING AN ERROR MESSAGE WHEN
SUBMITTING MY POST 9IT SAID ITR WOULDN'T POST!).. SORRY.

"lpjennifer" wrote:

Can someone point me in the right direction to accomplish the following.. I
want to add a subform (I think this is what i need??) to another existing
form. The subform needs to include a listbox (only to include 1 combo box
and 1 text box). The combo box (account #) values will come from a table
field and the text box is an amount ($$) field.. that the user enters in.
The user may have as many records (accts/amts) as necessary. After the first
combo box value is selected i would like the next line/record to appear
(cursor to go to the next line and where the user can enter another
account/amt).. I've been getting no where with this.. anyone have advice?
thanks.

Lpjennifer,

There is a bit of a confusion with terminology. A listbox is a control
inits own right. You can't have a combobox and/or a textbox inside a
listbox.

First of all you need a form, and it sounds like this should be a
Continuous View form (i.e. so you can see multiple records at once, in
tabular layout.) Do you have a table with the AccountNumber and Amount
fields which this form will be bouind to? If so, just make your form
based on this table. Then, it sounds like you have another table, which
is a "master list" of account numbers. So, on your form, you put a
combobox instead of a textbox to be bound to the AccountNumber field,
and you set the Row Source property of the combobox to the name of the
"master list" table.

And now, you want this to be a subform on another form. Are you sure
you need a subform? What is the relationship between the form with the
AccountNumber and Amount, and the other form that it will be a subform of?

Sorry, not a complete answer, but hopefully give you something you can
work with. If you need more help, please post back with more details,
maybe with examples, of what you are trying to achieve here.

--
Steve Schapel, Microsoft Access MVP


lpjennifer wrote:
Can someone point me in the right direction to accomplish the following.. I
want to add a subform (I think this is what i need??) to another existing
form. The subform needs to include a listbox (only to include 1 combo box
and 1 text box). The combo box (account #) values will come from a table
field and the text box is an amount ($$) field.. that the user enters in.
The user may have as many records (accts/amts) as necessary. After the first
combo box value is selected i would like the next line/record to appear
(cursor to go to the next line and where the user can enter another
account/amt).. I've been getting no where with this.. anyone have advice?
thanks.

Hi Steve.. thanks so much for helpig me out. I will try and better explain
what i am trying to do here. fyi first.. this system i am currently
re-writing was written in access 97.. and the reason i am re-writing the
system is bc we only have the .mde file.. and we are upgrading operating
system/software... so i am re-writing in access 2002. Everything has to
act/look very similiar to the current system. I have a check request window
that is filled out and each check can be broken out into multiple accounts.
The current window/form has 2 parts (1 - general info for check request, 2
(bottom half of window) - is where the accounts/amounts for that check
request are entered. It looks like a listbox to me (but i guess it isn't)..
1 column is account # combo box (values pulled from the master account#
table) and the 2nd column has an amount field (user enters this amount in -
this info is then stored in another table). It seems to me that the account #
and amount field act as a record because after selecting an account, a new
line/record below opens to enter another account/amount. Does this better
explain?.. i think the trouble i'm having here is having the acct# combo box
and amount text box be linked to one another (acting like a record)?? - I
think this is what i need.. sound like it to you? thanks again for all your
help! - jenn
"Steve Schapel" wrote:

Lpjennifer,

There is a bit of a confusion with terminology. A listbox is a control
inits own right. You can't have a combobox and/or a textbox inside a
listbox.

First of all you need a form, and it sounds like this should be a
Continuous View form (i.e. so you can see multiple records at once, in
tabular layout.) Do you have a table with the AccountNumber and Amount
fields which this form will be bouind to? If so, just make your form
based on this table. Then, it sounds like you have another table, which
is a "master list" of account numbers. So, on your form, you put a
combobox instead of a textbox to be bound to the AccountNumber field,
and you set the Row Source property of the combobox to the name of the
"master list" table.

And now, you want this to be a subform on another form. Are you sure
you need a subform? What is the relationship between the form with the
AccountNumber and Amount, and the other form that it will be a subform of?

Sorry, not a complete answer, but hopefully give you something you can
work with. If you need more help, please post back with more details,
maybe with examples, of what you are trying to achieve here.

--
Steve Schapel, Microsoft Access MVP


lpjennifer wrote:
Can someone point me in the right direction to accomplish the following.. I
want to add a subform (I think this is what i need??) to another existing
form. The subform needs to include a listbox (only to include 1 combo box
and 1 text box). The combo box (account #) values will come from a table
field and the text box is an amount ($$) field.. that the user enters in.
The user may have as many records (accts/amts) as necessary. After the first
combo box value is selected i would like the next line/record to appear
(cursor to go to the next line and where the user can enter another
account/amt).. I've been getting no where with this.. anyone have advice?
thanks.

actually i am getting a little somewhere.. the subform should be set to
Dataset view.. but i am still having some trouble.. with the dataset view..
it displays lots of records (that the user can enter data in).. displays like
an empty table (i realize it should bc it is dataset view, but the current
system does not do this).. i don't want this.. i would like.. once the user
selects an account then the next record will be available for another
acct/amount entered.. i don't want to see a list of empty records... i don't
know if i made this understandable.. hope so.. and 2 more minor things.. why
when i run the form.. the the back color and forecolor of my combo box and
text box don't take effect?.. just plain old white and grey.. like table
view.. again.. current system backcolor and forecolor aren't like this.. and
why when i select an acct number (from the combo box, all of the combo boxes
values change to the one i selected?.. oh yea.. you asked before if i thought
i needed this form to be a subform and i think i do.. there is a reference
number that links the 2 forms (and 2 tables, 1 table for general check
request info and another table holding the accts/amnts for that check
request)... so from this.. you think it should be a sub form right? thanks
again for all the help.

"lpjennifer" wrote:

Hi Steve.. thanks so much for helpig me out. I will try and better explain
what i am trying to do here. fyi first.. this system i am currently
re-writing was written in access 97.. and the reason i am re-writing the
system is bc we only have the .mde file.. and we are upgrading operating
system/software... so i am re-writing in access 2002. Everything has to
act/look very similiar to the current system. I have a check request window
that is filled out and each check can be broken out into multiple accounts.
The current window/form has 2 parts (1 - general info for check request, 2
(bottom half of window) - is where the accounts/amounts for that check
request are entered. It looks like a listbox to me (but i guess it isn't)..
1 column is account # combo box (values pulled from the master account#
table) and the 2nd column has an amount field (user enters this amount in -
this info is then stored in another table). It seems to me that the account #
and amount field act as a record because after selecting an account, a new
line/record below opens to enter another account/amount. Does this better
explain?.. i think the trouble i'm having here is having the acct# combo box
and amount text box be linked to one another (acting like a record)?? - I
think this is what i need.. sound like it to you? thanks again for all your
help! - jenn
"Steve Schapel" wrote:

Lpjennifer,

There is a bit of a confusion with terminology. A listbox is a control
inits own right. You can't have a combobox and/or a textbox inside a
listbox.

First of all you need a form, and it sounds like this should be a
Continuous View form (i.e. so you can see multiple records at once, in
tabular layout.) Do you have a table with the AccountNumber and Amount
fields which this form will be bouind to? If so, just make your form
based on this table. Then, it sounds like you have another table, which
is a "master list" of account numbers. So, on your form, you put a
combobox instead of a textbox to be bound to the AccountNumber field,
and you set the Row Source property of the combobox to the name of the
"master list" table.

And now, you want this to be a subform on another form. Are you sure
you need a subform? What is the relationship between the form with the
AccountNumber and Amount, and the other form that it will be a subform of?

Sorry, not a complete answer, but hopefully give you something you can
work with. If you need more help, please post back with more details,
maybe with examples, of what you are trying to achieve here.

--
Steve Schapel, Microsoft Access MVP


lpjennifer wrote:
Can someone point me in the right direction to accomplish the following.. I
want to add a subform (I think this is what i need??) to another existing
form. The subform needs to include a listbox (only to include 1 combo box
and 1 text box). The combo box (account #) values will come from a table
field and the text box is an amount ($$) field.. that the user enters in.
The user may have as many records (accts/amts) as necessary. After the first
combo box value is selected i would like the next line/record to appear
(cursor to go to the next line and where the user can enter another
account/amt).. I've been getting no where with this.. anyone have advice?
thanks.

Jenn,

Well, even though you are stuck with an old MDE file, you can still see
the design of the tables, I presume, and also access to the relationship
diagram if there is one. So this will help you to know what's going on.
I would imagine that there is a table that forms the basis of the
record source for the first form, i.e. check request info, and
presumably this table will have a primary key field, let's say something
like RequestID. And then, if I understand you correctly, there can be
multiple amounts entered for each request. So I would imagine there is
another table for the amounts, and there should be a field in this table
to act as a foreign key field to the RequestID or whatever it's called.
So yes, I think you are right, the Amounts form is placed on the
Requests form as a subform, and the RequestID will be entered in the
LinkMasterFields and LinkChildFields properties of the subform.

It is a mystery to me why you are seeing a whole lot of blank records in
the subform. By default, a subform in continuous view would only show
amount records associated with the current main form request, plus one
extra blank line for a new record to be entered. I have never used a
datasheet view form, but I believe this would be the same. Just make
sure the table structure and form/subform design is basically as I have
described above, and if it is still happening, please post back with
some more specific details.

On your other question... As far as I know, you can't control the
colour of a datasheet.

--
Steve Schapel, Microsoft Access MVP

lpjennifer wrote:
actually i am getting a little somewhere.. the subform should be set to
Dataset view.. but i am still having some trouble.. with the dataset view..
it displays lots of records (that the user can enter data in).. displays like
an empty table (i realize it should bc it is dataset view, but the current
system does not do this).. i don't want this.. i would like.. once the user
selects an account then the next record will be available for another
acct/amount entered.. i don't want to see a list of empty records... i don't
know if i made this understandable.. hope so.. and 2 more minor things.. why
when i run the form.. the the back color and forecolor of my combo box and
text box don't take effect?.. just plain old white and grey.. like table
view.. again.. current system backcolor and forecolor aren't like this.. and
why when i select an acct number (from the combo box, all of the combo boxes
values change to the one i selected?.. oh yea.. you asked before if i thought
i needed this form to be a subform and i think i do.. there is a reference
number that links the 2 forms (and 2 tables, 1 table for general check
request info and another table holding the accts/amnts for that check
request)... so from this.. you think it should be a sub form right? thanks
again for all the help.

"lpjennifer" wrote:


Hi Steve.. thanks so much for helpig me out. I will try and better explain
what i am trying to do here. fyi first.. this system i am currently
re-writing was written in access 97.. and the reason i am re-writing the
system is bc we only have the .mde file.. and we are upgrading operating
system/software... so i am re-writing in access 2002. Everything has to
act/look very similiar to the current system. I have a check request window
that is filled out and each check can be broken out into multiple accounts.
The current window/form has 2 parts (1 - general info for check request, 2
(bottom half of window) - is where the accounts/amounts for that check
request are entered. It looks like a listbox to me (but i guess it isn't)..
1 column is account # combo box (values pulled from the master account#
table) and the 2nd column has an amount field (user enters this amount in -
this info is then stored in another table). It seems to me that the account #
and amount field act as a record because after selecting an account, a new
line/record below opens to enter another account/amount. Does this better
explain?.. i think the trouble i'm having here is having the acct# combo box
and amount text box be linked to one another (acting like a record)?? - I
think this is what i need.. sound like it to you? thanks again for all your
help! - jenn

hey Steve.. i really do appreciate your involvement with my problem here. I
took a look at the sample db in access (bc a sub form is used) but of
course.. things vary.. i did look at the relationship and tables of my mde
file.. i did set the linkMaster and linkchild fields to my "ID" field... Do
these links have to do with when the information 9on the screen) is saved?..
i only ask bc this form/subform is for entering a new check request (and
entering in their corresponding accts/amts).. this is not a form used for
lookup (that's another form).. so anyways.. i guess im getting somewhere..
slowly.. but now when i open up the new form i see my subform (and the
combobox and it's values) but am unable to select a value from it.. i've
added the following stmnt to my command button (on main form) that saves the
information the user entered but i am receiving a msg (the command or action
"Saverecord' isn't available now).. should i have also put this stmnt in the
subform's code?.. DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

Thanks again for all of your assistance steve!

- Jenn

"Steve Schapel" wrote:

Jenn,

Well, even though you are stuck with an old MDE file, you can still see
the design of the tables, I presume, and also access to the relationship
diagram if there is one. So this will help you to know what's going on.
I would imagine that there is a table that forms the basis of the
record source for the first form, i.e. check request info, and
presumably this table will have a primary key field, let's say something
like RequestID. And then, if I understand you correctly, there can be
multiple amounts entered for each request. So I would imagine there is
another table for the amounts, and there should be a field in this table
to act as a foreign key field to the RequestID or whatever it's called.
So yes, I think you are right, the Amounts form is placed on the
Requests form as a subform, and the RequestID will be entered in the
LinkMasterFields and LinkChildFields properties of the subform.

It is a mystery to me why you are seeing a whole lot of blank records in
the subform. By default, a subform in continuous view would only show
amount records associated with the current main form request, plus one
extra blank line for a new record to be entered. I have never used a
datasheet view form, but I believe this would be the same. Just make
sure the table structure and form/subform design is basically as I have
described above, and if it is still happening, please post back with
some more specific details.

On your other question... As far as I know, you can't control the
colour of a datasheet.

--
Steve Schapel, Microsoft Access MVP

lpjennifer wrote:
actually i am getting a little somewhere.. the subform should be set to
Dataset view.. but i am still having some trouble.. with the dataset view..
it displays lots of records (that the user can enter data in).. displays like
an empty table (i realize it should bc it is dataset view, but the current
system does not do this).. i don't want this.. i would like.. once the user
selects an account then the next record will be available for another
acct/amount entered.. i don't want to see a list of empty records... i don't
know if i made this understandable.. hope so.. and 2 more minor things.. why
when i run the form.. the the back color and forecolor of my combo box and
text box don't take effect?.. just plain old white and grey.. like table
view.. again.. current system backcolor and forecolor aren't like this.. and
why when i select an acct number (from the combo box, all of the combo boxes
values change to the one i selected?.. oh yea.. you asked before if i thought
i needed this form to be a subform and i think i do.. there is a reference
number that links the 2 forms (and 2 tables, 1 table for general check
request info and another table holding the accts/amnts for that check
request)... so from this.. you think it should be a sub form right? thanks
again for all the help.

"lpjennifer" wrote:


Hi Steve.. thanks so much for helpig me out. I will try and better explain
what i am trying to do here. fyi first.. this system i am currently
re-writing was written in access 97.. and the reason i am re-writing the
system is bc we only have the .mde file.. and we are upgrading operating
system/software... so i am re-writing in access 2002. Everything has to
act/look very similiar to the current system. I have a check request window
that is filled out and each check can be broken out into multiple accounts.
The current window/form has 2 parts (1 - general info for check request, 2
(bottom half of window) - is where the accounts/amounts for that check
request are entered. It looks like a listbox to me (but i guess it isn't)..
1 column is account # combo box (values pulled from the master account#
table) and the 2nd column has an amount field (user enters this amount in -
this info is then stored in another table). It seems to me that the account #
and amount field act as a record because after selecting an account, a new
line/record below opens to enter another account/amount. Does this better
explain?.. i think the trouble i'm having here is having the acct# combo box
and amount text box be linked to one another (acting like a record)?? - I
think this is what i need.. sound like it to you? thanks again for all your
help! - jenn

Jenn

After you have entered a Request record on your main form, then
(assuming the Link Child Fields and Link Master Fields settings are
correct) any records entered into the subform will automatically be
assigned the ID number of the main form record, which is what you want.

There is no need at all to explicitly save the main form record, this
will happen automatically. In any case, I would recommend always
avoiding the use of DoCmd.DoMenuItem it is not good code.

As for not being able to enter a record onthe subform, I am afraid I
can't think of why this would happen. Do you get an error message, or
what actually happens when you try? Check the properties of the form
that is used for the subform... Are AllowAdditions and AllowEdits both
set to Yes?

--
Steve Schapel, Microsoft Access MVP


lpjennifer wrote:
hey Steve.. i really do appreciate your involvement with my problem here. I
took a look at the sample db in access (bc a sub form is used) but of
course.. things vary.. i did look at the relationship and tables of my mde
file.. i did set the linkMaster and linkchild fields to my "ID" field... Do
these links have to do with when the information 9on the screen) is saved?..
i only ask bc this form/subform is for entering a new check request (and
entering in their corresponding accts/amts).. this is not a form used for
lookup (that's another form).. so anyways.. i guess im getting somewhere..
slowly.. but now when i open up the new form i see my subform (and the
combobox and it's values) but am unable to select a value from it.. i've
added the following stmnt to my command button (on main form) that saves the
information the user entered but i am receiving a msg (the command or action
"Saverecord' isn't available now).. should i have also put this stmnt in the
subform's code?.. DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

Thanks again for all of your assistance steve!

- Jenn

yea.. those properties are set to yes.. but i thought i'd test out the main
form.. so i made a acopy of it and deleted the sub form.. i guess i have
something set up wrong on the main form.. bc when i close out of that form
9w/out the sub form my entry isn't saved.. is there something i have to
specifically set/code with the ID number being an auto-number (system
generated)? my form's recordset property is set to the table where i want
the info. saved.. i'll see what i can come up with.. thanks! - Jenn

"Steve Schapel" wrote:

Jenn

After you have entered a Request record on your main form, then
(assuming the Link Child Fields and Link Master Fields settings are
correct) any records entered into the subform will automatically be
assigned the ID number of the main form record, which is what you want.

There is no need at all to explicitly save the main form record, this
will happen automatically. In any case, I would recommend always
avoiding the use of DoCmd.DoMenuItem it is not good code.

As for not being able to enter a record onthe subform, I am afraid I
can't think of why this would happen. Do you get an error message, or
what actually happens when you try? Check the properties of the form
that is used for the subform... Are AllowAdditions and AllowEdits both
set to Yes?

--
Steve Schapel, Microsoft Access MVP


lpjennifer wrote:
hey Steve.. i really do appreciate your involvement with my problem here. I
took a look at the sample db in access (bc a sub form is used) but of
course.. things vary.. i did look at the relationship and tables of my mde
file.. i did set the linkMaster and linkchild fields to my "ID" field... Do
these links have to do with when the information 9on the screen) is saved?..
i only ask bc this form/subform is for entering a new check request (and
entering in their corresponding accts/amts).. this is not a form used for
lookup (that's another form).. so anyways.. i guess im getting somewhere..
slowly.. but now when i open up the new form i see my subform (and the
combobox and it's values) but am unable to select a value from it.. i've
added the following stmnt to my command button (on main form) that saves the
information the user entered but i am receiving a msg (the command or action
"Saverecord' isn't available now).. should i have also put this stmnt in the
subform's code?.. DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

Thanks again for all of your assistance steve!

- Jenn

Jenn,

The only reason I know of for this happening is if there is a "required"
field which hasn't had data entered into it, or a Validation Rule set
within the form or the table that hasn't been complied with in the data
which was entered. The use of an AUtonumber ID field shouldn't have any
effect. If you have the form set up correctly, then any new record
entetred should automatically be saved to the table when the form is
closed, or you move to a new record, or you move the focus to the subform.

--
Steve Schapel, Microsoft Access MVP


lpjennifer wrote:
yea.. those properties are set to yes.. but i thought i'd test out the main
form.. so i made a acopy of it and deleted the sub form.. i guess i have
something set up wrong on the main form.. bc when i close out of that form
9w/out the sub form my entry isn't saved.. is there something i have to
specifically set/code with the ID number being an auto-number (system
generated)? my form's recordset property is set to the table where i want
the info. saved.. i'll see what i can come up with.. thanks! - Jenn