incorporate a combo box and text box within same listbox

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,

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.
 
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
 
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.
 
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.
 
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

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?
 
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
 
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.
 
OK.. i got the record to save.. the main form information anyway.. not the
subform info. yet.. i still can't even select a value from the combo box on
the subform.. i can see them and scroll through them.. but if i try and
select one.. it does nothing.. no error either.. but i am feeling better..
now that i have made some progress.. thanks again.. for your time steve!
 
c

Steve Schapel said:
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.
Click to expand...
 
Jenn,

Well, I guess that's progress.

Strange about the subform. Can you let us know the name of the fields
in the subform, the settings you have for the subform's Link Child
Fields and Link Master Fields properties, and details of the combobox on
the subform... which field it is bound to, and what is its RowSource?
What happens if you try to enter something in the other textbox on the
subform? Is the subform based on a table or a query? If a query, can
you open the query independently and enter a new record in there?
 
Hi Steve..
The subform field names are:

"txtAccount", "txtAmount", "txtTotalAmount".

The settings I have for the subform's Link Master field
property is "ID" (when I add "ID" to both the child and
master field the subform doesn't look right (within the
main form.. when run.. it doesn't even make the combo box
and amount fields visible).

The subform's record source is set to
tblCheckReqAmtsToAccts (table where I want the
accts/amounts saved to).

The account combobox's rowsource is a select statement
(selects account numbers from tblAccounts table) and the
txtAmount field is a text field that the user enters in.
Both of these fields' control source is set to nothing..
because if I set them to the field name (of the table
where I want them stored - tblCheckReqAmtsToAccts) I get a
list of all the current acct/amts entries in the table.

I also just realized that when I set either link child or
link master - the main form doesn't let me make any
selections (I'e. I see the drop-down values but when I go
to highlight one it doesn't accept it.. no error.. just
won't accept).

Wait.. this may be the obvious problem.. I don't have
this "ID" field anywhere on my subform.. (or my main form
for that matter) it is in the subform's table (where I
want to info. stored) but nowhere on the subform.. should
I create a "ID" textbox and just male the visible propery
= false.. because I don't want it shown on the screen.

Thanks Steve! - Jenn
 
I did just realize that when i TRY to do something in the
subform (like selecting an acct from the drop-down).. i
can then no longer select anything in the main form.
 
Jenn,

You will have to set the Control Source of the combobox and textbox on
the subform to the txtAccount and txtAmount fields in the table. The
fact that you see all the records is to be expected, and the solution is
to get the main/subform link working properly, not to make the controls
unbound! What is the story with the txtTotalAmount field? And are
there any other fields in the tblCheckReqAmtsToAccts table which are not
represented on the subform?

It should not be necessary to put a hidden control on the subform bound
to the ID field... but not a bad idea to do so, I always do this in such
cases just to help make it easier to see what's going on. Plus you can
make it visible in the meantime while trying to track down the problem.
But it would certainly be a good idea to put the ID field from the
main table on the main form. What is the data type of the ID field in
the main table? And what is the data type of the ID field in the
tblCheckReqAmtsToAccts table?

We will get this figured out eventually!
 
I did take your advice on setting the combobox and textbox
control source set to the table account and amounts
field. This does make sense to me because this is how I
set up the main form screen (and it saves the info. like
it should). What does not make sense to me is when you
say 'the fact that you see all the records is to be
expected'.. I don't understand because this is for the
user to select an account (from the drop-down box -
values from my accounts table) and for the user to
manually enter the amount in (for that account) and then
this information get added to my CheckReqAmtsToAccounts
table. The txtTotalAmount field is placed in the footer
of my subform.. I want this field to sum up the account
amounts (as they enter them in). The only fields in my
CheckReqAmtsToAccounts table are ID, Amount, Date (this
value = CurrentDate), and AcctNumber.

I also added the ID field to both forms (main and sub) and
for now.. like you said.. I made them visible.. to see
what's going on. The main form's ID is good.. it's the
next # (auto-number) but the ID field in my sub-form is no
good.. I think because of me setting it's control source
to my ID field in the table (CheckReqAmtsToAccounts). this
value is the value of the first record in that table.. but
instead it should match the main form ID (which is a new
ID #). The ID field in my CheckRequests table is defined
as autoNumber (primary key, no duplicates allowed) and the
ID field in the CheckReqAmtstoAccounts field is defined as
a number (NOT SET AS primary key, duplicates ok).

Thanks Steve!
 
I did take your advice on setting the combobox and textbox
control source set to the table account and amounts
field. This does make sense to me because this is how I
set up the main form screen (and it saves the info. like
it should). What does not make sense to me is when you
say 'the fact that you see all the records is to be
expected'.. I don't understand because this is for the
user to select an account (from the drop-down box -
values from my accounts table) and for the user to
manually enter the amount in (for that account) and then
this information get added to my CheckReqAmtsToAccounts
table. The txtTotalAmount field is placed in the footer
of my subform.. I want this field to sum up the account
amounts (as they enter them in). The only fields in my
CheckReqAmtsToAccounts table are ID, Amount, Date (this
value = CurrentDate), and AcctNumber.

I also added the ID field to both forms (main and sub) and
for now.. like you said.. I made them visible.. to see
what's going on. The main form's ID is good.. it's the
next # (auto-number) but the ID field in my sub-form is no
good.. I think because of me setting it's control source
to my ID field in the table (CheckReqAmtsToAccounts). this
value is the value of the first record in that table.. but
instead it should match the main form ID (which is a new
ID #). The ID field in my CheckRequests table is defined
as autoNumber (primary key, no duplicates allowed) and the
ID field in the CheckReqAmtstoAccounts field is defined as
a number (NOT SET AS primary key, duplicates ok).
 
Jenn,

Well, what you have described is how it should be, so I still don't
understand what the problem is. If you set the Link Child Fields and
Link Master Fields properties of the subform to [ID] assuming this is
the name of the field in both tables, then the subform should only show
those CheckReqAmtsToAccounts records which match the current record on
the main form. And any new record added to the subform should
automatically have the ID field entered as the ID of the current record
on the main form. This is the whole idea of a subform, so if it doesn't
work it is very puzzling, but there must be a simple explanation that we
are missing at the moment.
 
Thanks Steve.. i guess i was misunderstanding.. the main
form is to enter a new check request.. not look up an
existing one.. so the subform will never show records for
the check request (bc it is new).. the reason i wanted
(and thought was right to do) a subform is for the user to
enter the accounts/amounts for that new check request they
are entering... then these NEW accounts/amounts (for this
NEW checkc request) would be added to my
CheckRequestAmntsToAccounts table (and the generated ID
field - off the main form is what the subform's ID field
would be).. to link the check request with all the
accounts/amts that go with it. sorry for the
misunderstanding.
-----Original Message-----
Jenn,

Well, what you have described is how it should be, so I still don't
understand what the problem is. If you set the Link Child Fields and
Link Master Fields properties of the subform to [ID] assuming this is
the name of the field in both tables, then the subform should only show
those CheckReqAmtsToAccounts records which match the current record on
the main form. And any new record added to the subform should
automatically have the ID field entered as the ID of the current record
on the main form. This is the whole idea of a subform, so if it doesn't
work it is very puzzling, but there must be a simple explanation that we
are missing at the moment.

--
Steve Schapel, Microsoft Access MVP
I did take your advice on setting the combobox and textbox
control source set to the table account and amounts
field. This does make sense to me because this is how I
set up the main form screen (and it saves the info. like
it should). What does not make sense to me is when you
say 'the fact that you see all the records is to be
expected'.. I don't understand because this is for the
user to select an account (from the drop-down box -
values from my accounts table) and for the user to
manually enter the amount in (for that account) and then
this information get added to my CheckReqAmtsToAccounts
table. The txtTotalAmount field is placed in the footer
of my subform.. I want this field to sum up the account
amounts (as they enter them in). The only fields in my
CheckReqAmtsToAccounts table are ID, Amount, Date (this
value = CurrentDate), and AcctNumber.

I also added the ID field to both forms (main and sub) and
for now.. like you said.. I made them visible.. to see
what's going on. The main form's ID is good.. it's the
next # (auto-number) but the ID field in my sub-form is no
good.. I think because of me setting it's control source
to my ID field in the table (CheckReqAmtsToAccounts). this
value is the value of the first record in that table.. but
instead it should match the main form ID (which is a new
ID #). The ID field in my CheckRequests table is defined
as autoNumber (primary key, no duplicates allowed) and the
ID field in the CheckReqAmtstoAccounts field is defined as
a number (NOT SET AS primary key, duplicates ok).

Thanks Steve!
Click to expand...
.
Click to expand...
 
Back
Top