data entry methods

  • Thread starter Thread starter Joanne
  • Start date Start date
J

Joanne

My db has 2 tables, CoName and CoLocation. It is just a simple little
app that will help save me loads of time and typing/writing.

I have some data that I will put in the tables to get started, but
what I want to be able to do is to add data to the tables as I use the
app. I thought to use combo boxes to populate my form, and if the
data I need is not in the table, I would like to add it to the table
right then and there thru the combo box and have it write to the table
so that I will be able to use it the next time I need it.

Can this be done? Using combo boxes? or should I use List Boxes?
I'm just trying to avoid putting data in the tables until I prove I
will need it (pure laziness).

Any help you can give me would be much appreciated.

TIA
Joanne
 
If you set the Limit to List property of the combo boxes to "No", you
should be able to put new values in. Don't forget to requery the combo
boxes if you do that

John
 
Thank you John
J. Goddard said:
If you set the Limit to List property of the combo boxes to "No", you
should be able to put new values in. Don't forget to requery the combo
boxes if you do that

John
Click to expand...
 
John
Did as advised, setting Limit to List of the cbobox to No, but I
cannot type in any info.
Any ideas why not?
TIA
Joanne
 
Hi -

A couple of questions -

What is the recordsource for the form you have your combo boxes on?
What is the Row Source Type and Row Source of the combo boxes?

Thanks

John
 
John
Record Source for form is blank

row source type for cboboxes is table/query

row source for cboName is;
SELECT tblMain.Name FROM tblMain ORDER BY [Name];

Row source for cboLocation is:
qryLocation
Here is the qry:
SELECT DISTINCTROW tblMain.location
FROM tblMain
WHERE (((tblMain.Name) Like Forms!frmPhoneOrders!cboName));

Problem #1
When I type something in the combo boxes it does accept the data but
it does not write the data to the table and as a result when I reload
the form the data is not there.

Problem #2
When I choose a Co Name, I want the cboLocation to show me only those
locations that are relevant to my choice instead of all locations in
the table. This works once, but when I choose a second co name, I
still get the locations that belong to the first choice. I have put
docmd.requery in both the gotfocus and lostfocus of cboLocation but
this has not helped bring up the correct list of locations on the
second use. Am stymied here, not sure what to try next. I did try
requerying cboName but that didn't do any good either.

Thanks for your efforts here - I sure appreciate you giving me your
time and expertise.

Joanne
 
Hi -

For problem 1:
It does not append to the table because the control is not bound to a
field in a table. The row source for the combo box is just that - a
source that gives the combo box its data.

Problem 2:
Update the rowsource for combo box 2 in the after update event of combo
box 1:

me!cboLocation.rowsource = "select tblmain.[location] from tblmain where
tblmain.[name] ='" & me!cboName & "'"

But now Problem 1 arises again - because you can't update tblmain, your
lists will never change.

You will have to update tblMain with code, or have tblMain as the form
record source and bind the two combo boxes to the appropriate table fields.

What is the form being used for?

John

John
Record Source for form is blank

row source type for cboboxes is table/query

row source for cboName is;
SELECT tblMain.Name FROM tblMain ORDER BY [Name];

Row source for cboLocation is:
qryLocation
Here is the qry:
SELECT DISTINCTROW tblMain.location
FROM tblMain
WHERE (((tblMain.Name) Like Forms!frmPhoneOrders!cboName));

Problem #1
When I type something in the combo boxes it does accept the data but
it does not write the data to the table and as a result when I reload
the form the data is not there.

Problem #2
When I choose a Co Name, I want the cboLocation to show me only those
locations that are relevant to my choice instead of all locations in
the table. This works once, but when I choose a second co name, I
still get the locations that belong to the first choice. I have put
docmd.requery in both the gotfocus and lostfocus of cboLocation but
this has not helped bring up the correct list of locations on the
second use. Am stymied here, not sure what to try next. I did try
requerying cboName but that didn't do any good either.

Thanks for your efforts here - I sure appreciate you giving me your
time and expertise.

Joanne



J. Goddard wrote:

Hi -

A couple of questions -

What is the recordsource for the form you have your combo boxes on?
What is the Row Source Type and Row Source of the combo boxes?

Thanks

John


Joanne wrote:
Click to expand...
Click to expand...
 
Hi John
Thanks for your help. I now have the app running almost the way I want
it to, but of course we always want to tweak it to be even better ;-).

The purpose of this app is to produce what were hand-written phone
orders, where much repeating was necessary because items could be
placed on the order only if they all came from the same mfg. Mostly
that is not the case, so I end up writing in the city state name
contact etc etc over and over for each mfg.

I have a table called tblMain
In it are 2 columns, Name (name of company) and Location (many
companies have several locations
example
Kelloggs Chicago
Kelloggs New York
Kelloggs Miami
Pella Rockford
Pella Birmingham
etc etc

When I click cboName to choose the Company name, the name is in the
list once for each location (can be repeated as much as 15-20 times).
I then, using your advice to requery the cboLocation on after update
of the cboName, go to cboLocation and get a list of cities relevant to
the company name I chose (these are in the list only once as
expected).
I would like to have the company name show up only once in the list
instead of once for each time it is in the table with a different
location.

Can this be done? Do I need to create another table with just the
company names and use it to populate the list, then use the second
table to tie the company Name and company Location together? I'm
afraid that if I do this how will I be able to do data entry from the
form which is what I am trying to do.

Also, please tell me how to bind the cboboxes to the row source. In
the list of properties, the 'bound' property is set to column 1 which
is correct, but I have a feeling this is not enough because I still
cannot get new data in my tables. Right now I have the rowsource for
both cboboxes set to their column from tblMain.

Again, thanks a heap for your time.
Joanne
J. Goddard said:
Hi -

For problem 1:
It does not append to the table because the control is not bound to a
field in a table. The row source for the combo box is just that - a
source that gives the combo box its data.

Problem 2:
Update the rowsource for combo box 2 in the after update event of combo
box 1:

me!cboLocation.rowsource = "select tblmain.[location] from tblmain where
tblmain.[name] ='" & me!cboName & "'"

But now Problem 1 arises again - because you can't update tblmain, your
lists will never change.

You will have to update tblMain with code, or have tblMain as the form
record source and bind the two combo boxes to the appropriate table fields.

What is the form being used for?

John

John
Record Source for form is blank

row source type for cboboxes is table/query

row source for cboName is;
SELECT tblMain.Name FROM tblMain ORDER BY [Name];

Row source for cboLocation is:
qryLocation
Here is the qry:
SELECT DISTINCTROW tblMain.location
FROM tblMain
WHERE (((tblMain.Name) Like Forms!frmPhoneOrders!cboName));

Problem #1
When I type something in the combo boxes it does accept the data but
it does not write the data to the table and as a result when I reload
the form the data is not there.

Problem #2
When I choose a Co Name, I want the cboLocation to show me only those
locations that are relevant to my choice instead of all locations in
the table. This works once, but when I choose a second co name, I
still get the locations that belong to the first choice. I have put
docmd.requery in both the gotfocus and lostfocus of cboLocation but
this has not helped bring up the correct list of locations on the
second use. Am stymied here, not sure what to try next. I did try
requerying cboName but that didn't do any good either.

Thanks for your efforts here - I sure appreciate you giving me your
time and expertise.

Joanne



J. Goddard wrote:

Hi -

A couple of questions -

What is the recordsource for the form you have your combo boxes on?
What is the Row Source Type and Row Source of the combo boxes?

Thanks

John


Joanne wrote:


John
Did as advised, setting Limit to List of the cbobox to No, but I
cannot type in any info.
Any ideas why not?
TIA
Joanne
J. Goddard wrote:



If you set the Limit to List property of the combo boxes to "No", you
should be able to put new values in. Don't forget to requery the combo
boxes if you do that

John


Joanne wrote:



My db has 2 tables, CoName and CoLocation. It is just a simple little
app that will help save me loads of time and typing/writing.

I have some data that I will put in the tables to get started, but
what I want to be able to do is to add data to the tables as I use the
app. I thought to use combo boxes to populate my form, and if the
data I need is not in the table, I would like to add it to the table
right then and there thru the combo box and have it write to the table
so that I will be able to use it the next time I need it.

Can this be done? Using combo boxes? or should I use List Boxes?
I'm just trying to avoid putting data in the tables until I prove I
will need it (pure laziness).

Any help you can give me would be much appreciated.

TIA
Joanne
Click to expand...
Click to expand...
Click to expand...
 
Hi -

To solve the repeating company name problem -

change the row source for cboName from:

SELECT tblMain.Name FROM tblMain ORDER BY [Name];
to
SELECT DISTINCT tblMain.Name FROM tblMain ORDER BY [Name];
Also, please tell me how to bind the cboboxes to the row source. In
the list of properties, the 'bound' property is set to column 1 which
is correct, but I have a feeling this is not enough because I still
cannot get new data in my tables. Right now I have the rowsource for
both cboboxes set to their column from tblMain.
Click to expand...

That should work - I don't see why it wouldn't.

You could try putting [cboname].requery in the On enter event of
cboname; you do have to requery it at some point, otherwise it won't be
refreshed with any new company names.

One thing to remember is that when you open the form, the combo boxes
show the values of the current record (unless the form's Data entry
property is set to "Yes", in which case all you can do is add new
records) ; if you change the combo box values without going to a new
record first, all you are doing is changing the current record.

Are you sure you are always adding a new record?

John

Hi John
Thanks for your help. I now have the app running almost the way I want
it to, but of course we always want to tweak it to be even better ;-).

The purpose of this app is to produce what were hand-written phone
orders, where much repeating was necessary because items could be
placed on the order only if they all came from the same mfg. Mostly
that is not the case, so I end up writing in the city state name
contact etc etc over and over for each mfg.

I have a table called tblMain
In it are 2 columns, Name (name of company) and Location (many
companies have several locations
example
Kelloggs Chicago
Kelloggs New York
Kelloggs Miami
Pella Rockford
Pella Birmingham
etc etc

When I click cboName to choose the Company name, the name is in the
list once for each location (can be repeated as much as 15-20 times).
I then, using your advice to requery the cboLocation on after update
of the cboName, go to cboLocation and get a list of cities relevant to
the company name I chose (these are in the list only once as
expected).
I would like to have the company name show up only once in the list
instead of once for each time it is in the table with a different
location.

Can this be done? Do I need to create another table with just the
company names and use it to populate the list, then use the second
table to tie the company Name and company Location together? I'm
afraid that if I do this how will I be able to do data entry from the
form which is what I am trying to do.

Also, please tell me how to bind the cboboxes to the row source. In
the list of properties, the 'bound' property is set to column 1 which
is correct, but I have a feeling this is not enough because I still
cannot get new data in my tables. Right now I have the rowsource for
both cboboxes set to their column from tblMain.

Again, thanks a heap for your time.
Joanne
J. Goddard wrote:

Hi -

For problem 1:
It does not append to the table because the control is not bound to a
field in a table. The row source for the combo box is just that - a
source that gives the combo box its data.

Problem 2:
Update the rowsource for combo box 2 in the after update event of combo
box 1:

me!cboLocation.rowsource = "select tblmain.[location] from tblmain where
tblmain.[name] ='" & me!cboName & "'"

But now Problem 1 arises again - because you can't update tblmain, your
lists will never change.

You will have to update tblMain with code, or have tblMain as the form
record source and bind the two combo boxes to the appropriate table fields.

What is the form being used for?

John

John
Record Source for form is blank

row source type for cboboxes is table/query

row source for cboName is;
SELECT tblMain.Name FROM tblMain ORDER BY [Name];

Row source for cboLocation is:
qryLocation
Here is the qry:
SELECT DISTINCTROW tblMain.location
FROM tblMain
WHERE (((tblMain.Name) Like Forms!frmPhoneOrders!cboName));

Problem #1
When I type something in the combo boxes it does accept the data but
it does not write the data to the table and as a result when I reload
the form the data is not there.

Problem #2
When I choose a Co Name, I want the cboLocation to show me only those
locations that are relevant to my choice instead of all locations in
the table. This works once, but when I choose a second co name, I
still get the locations that belong to the first choice. I have put
docmd.requery in both the gotfocus and lostfocus of cboLocation but
this has not helped bring up the correct list of locations on the
second use. Am stymied here, not sure what to try next. I did try
requerying cboName but that didn't do any good either.

Thanks for your efforts here - I sure appreciate you giving me your
time and expertise.

Joanne



J. Goddard wrote:



Hi -

A couple of questions -

What is the recordsource for the form you have your combo boxes on?
What is the Row Source Type and Row Source of the combo boxes?

Thanks

John


Joanne wrote:



John
Did as advised, setting Limit to List of the cbobox to No, but I
cannot type in any info.
Any ideas why not?
TIA
Joanne
J. Goddard wrote:




If you set the Limit to List property of the combo boxes to "No", you
should be able to put new values in. Don't forget to requery the combo
boxes if you do that

John


Joanne wrote:




My db has 2 tables, CoName and CoLocation. It is just a simple little
app that will help save me loads of time and typing/writing.

I have some data that I will put in the tables to get started, but
what I want to be able to do is to add data to the tables as I use the
app. I thought to use combo boxes to populate my form, and if the
data I need is not in the table, I would like to add it to the table
right then and there thru the combo box and have it write to the table
so that I will be able to use it the next time I need it.

Can this be done? Using combo boxes? or should I use List Boxes?
I'm just trying to avoid putting data in the tables until I prove I
will need it (pure laziness).

Any help you can give me would be much appreciated.

TIA
Joanne
Click to expand...
Click to expand...
Click to expand...
 
Back
Top