g said:Thanks jeff.
My Combo box (Name: Item_List) in my form ( FormName: FormBorrower) row
source is from the table_ListItem (SELECT table_ListItem.[Title],
[table_ListItem.[Author], FROM table_ListItem;. What I wanted to do If the
item with title say for example "The Book 1" was barrowed if you create a
new record that Item should not show up anymore so it considered not
available because it is still out not yet returned. So how to do that?
Hope
this is more clear.
Jeff Boyce said:We aren't there. We can't see what you're working with. We don't know
how
your data is structured.
"How" depends on what you have.
More specific description may lead to more specific suggestions.
Regards
Jeff Boyce
Microsoft Office/Access MVP
Jeff Boyce said:If the item is borrowed, where is that fact recorded?
The generic approach you'd use is to modify the query that feeds the
combobox to exclude any that are "borrowed" (how depends on how you record
that fact).
Regards
Jeff Boyce
Microsoft Office/Access MVP
g said:Thanks jeff.
My Combo box (Name: Item_List) in my form ( FormName: FormBorrower) row
source is from the table_ListItem (SELECT table_ListItem.[Title],
[table_ListItem.[Author], FROM table_ListItem;. What I wanted to do If the
item with title say for example "The Book 1" was barrowed if you create a
new record that Item should not show up anymore so it considered not
available because it is still out not yet returned. So how to do that?
Hope
this is more clear.
Jeff Boyce said:We aren't there. We can't see what you're working with. We don't know
how
your data is structured.
"How" depends on what you have.
More specific description may lead to more specific suggestions.
Regards
Jeff Boyce
Microsoft Office/Access MVP
Hello again,
I have a combo box for selecting item to be borrowed. I wanted that on
the
next record that item that been selected or borrowed should not show up
anymore. How will I do this. Any Idea?
Thank you
g said:The form is bound into the tbl_Borrower. The combo box Item list is bound
to
the column of tbl_Borrower.ItemList. All the item that borrowed will
recorded
to the tbl_Borrower.
Jeff Boyce said:If the item is borrowed, where is that fact recorded?
The generic approach you'd use is to modify the query that feeds the
combobox to exclude any that are "borrowed" (how depends on how you
record
that fact).
Regards
Jeff Boyce
Microsoft Office/Access MVP
g said:Thanks jeff.
My Combo box (Name: Item_List) in my form ( FormName: FormBorrower) row
source is from the table_ListItem (SELECT table_ListItem.[Title],
[table_ListItem.[Author], FROM table_ListItem;. What I wanted to do If
the
item with title say for example "The Book 1" was barrowed if you
create a
new record that Item should not show up anymore so it considered not
available because it is still out not yet returned. So how to do that?
Hope
this is more clear.
:
We aren't there. We can't see what you're working with. We don't
know
how
your data is structured.
"How" depends on what you have.
More specific description may lead to more specific suggestions.
Regards
Jeff Boyce
Microsoft Office/Access MVP
Hello again,
I have a combo box for selecting item to be borrowed. I wanted that
on
the
next record that item that been selected or borrowed should not show
up
anymore. How will I do this. Any Idea?
Thank you
Jeff Boyce said:I'm still not very clear on the table structure you are using...
If you have a well-normalized table structure, you'll probably have three
tables:
tblBorrower (with information about folks who borrow)
tblItem (with information about items that get borrowed)
trelItemBorrowed (with information about who borrowed what and when and
when they returned it)
Does this match the table structure you are using?
Regards
Jeff Boyce
Microsoft Office/Access MVP
g said:The form is bound into the tbl_Borrower. The combo box Item list is bound
to
the column of tbl_Borrower.ItemList. All the item that borrowed will
recorded
to the tbl_Borrower.
Jeff Boyce said:If the item is borrowed, where is that fact recorded?
The generic approach you'd use is to modify the query that feeds the
combobox to exclude any that are "borrowed" (how depends on how you
record
that fact).
Regards
Jeff Boyce
Microsoft Office/Access MVP
Thanks jeff.
My Combo box (Name: Item_List) in my form ( FormName: FormBorrower) row
source is from the table_ListItem (SELECT table_ListItem.[Title],
[table_ListItem.[Author], FROM table_ListItem;. What I wanted to do If
the
item with title say for example "The Book 1" was barrowed if you
create a
new record that Item should not show up anymore so it considered not
available because it is still out not yet returned. So how to do that?
Hope
this is more clear.
:
We aren't there. We can't see what you're working with. We don't
know
how
your data is structured.
"How" depends on what you have.
More specific description may lead to more specific suggestions.
Regards
Jeff Boyce
Microsoft Office/Access MVP
Hello again,
I have a combo box for selecting item to be borrowed. I wanted that
on
the
next record that item that been selected or borrowed should not show
up
anymore. How will I do this. Any Idea?
Thank you
g said:I don't have the last table. tblBorrower & trelItemBorrowed are the same
table.
Jeff Boyce said:I'm still not very clear on the table structure you are using...
If you have a well-normalized table structure, you'll probably have three
tables:
tblBorrower (with information about folks who borrow)
tblItem (with information about items that get borrowed)
trelItemBorrowed (with information about who borrowed what and when
and
when they returned it)
Does this match the table structure you are using?
Regards
Jeff Boyce
Microsoft Office/Access MVP
g said:The form is bound into the tbl_Borrower. The combo box Item list is
bound
to
the column of tbl_Borrower.ItemList. All the item that borrowed will
recorded
to the tbl_Borrower.
:
If the item is borrowed, where is that fact recorded?
The generic approach you'd use is to modify the query that feeds the
combobox to exclude any that are "borrowed" (how depends on how you
record
that fact).
Regards
Jeff Boyce
Microsoft Office/Access MVP
Thanks jeff.
My Combo box (Name: Item_List) in my form ( FormName: FormBorrower)
row
source is from the table_ListItem (SELECT table_ListItem.[Title],
[table_ListItem.[Author], FROM table_ListItem;. What I wanted to do
If
the
item with title say for example "The Book 1" was barrowed if you
create a
new record that Item should not show up anymore so it considered not
available because it is still out not yet returned. So how to do
that?
Hope
this is more clear.
:
We aren't there. We can't see what you're working with. We don't
know
how
your data is structured.
"How" depends on what you have.
More specific description may lead to more specific suggestions.
Regards
Jeff Boyce
Microsoft Office/Access MVP
Hello again,
I have a combo box for selecting item to be borrowed. I wanted
that
on
the
next record that item that been selected or borrowed should not
show
up
anymore. How will I do this. Any Idea?
Thank you
Jeff Boyce said:Perhaps I don't understand your specific situation well enough...
In my experience, a Borrower can borrow more than one Item. That make it a
"one-to-many" relationship.
And over time, the same Item might be borrowed by more than one Borrower.
That makes the relationship between Borrower and Item a "many-to-many"
relationship, requiring a third table to resolve it (see my previous email).
How is your situation different?
Regards
Jeff Boyce
Microsoft Office/Access MVP
g said:I don't have the last table. tblBorrower & trelItemBorrowed are the same
table.
Jeff Boyce said:I'm still not very clear on the table structure you are using...
If you have a well-normalized table structure, you'll probably have three
tables:
tblBorrower (with information about folks who borrow)
tblItem (with information about items that get borrowed)
trelItemBorrowed (with information about who borrowed what and when
and
when they returned it)
Does this match the table structure you are using?
Regards
Jeff Boyce
Microsoft Office/Access MVP
The form is bound into the tbl_Borrower. The combo box Item list is
bound
to
the column of tbl_Borrower.ItemList. All the item that borrowed will
recorded
to the tbl_Borrower.
:
If the item is borrowed, where is that fact recorded?
The generic approach you'd use is to modify the query that feeds the
combobox to exclude any that are "borrowed" (how depends on how you
record
that fact).
Regards
Jeff Boyce
Microsoft Office/Access MVP
Thanks jeff.
My Combo box (Name: Item_List) in my form ( FormName: FormBorrower)
row
source is from the table_ListItem (SELECT table_ListItem.[Title],
[table_ListItem.[Author], FROM table_ListItem;. What I wanted to do
If
the
item with title say for example "The Book 1" was barrowed if you
create a
new record that Item should not show up anymore so it considered not
available because it is still out not yet returned. So how to do
that?
Hope
this is more clear.
:
We aren't there. We can't see what you're working with. We don't
know
how
your data is structured.
"How" depends on what you have.
More specific description may lead to more specific suggestions.
Regards
Jeff Boyce
Microsoft Office/Access MVP
Hello again,
I have a combo box for selecting item to be borrowed. I wanted
that
on
the
next record that item that been selected or borrowed should not
show
up
anymore. How will I do this. Any Idea?
Thank you
g said:You're absolutely Correct. "Borrower can barrow more than one item"
Selecting the Item to be borrowed is from the combo box/drop down list in
data sheet.
By using your normalized table:
Ex:
ITEM NAME: BOOK1, BOOK2, BOOK3, BOOK4, BOOK5, AND BOOK6
*Record #1 in my Form
BORROWER NAME
Borrower 1
*sub form in a data sheet
ITEM NAME BORROWED SELECTED RETURNED
Book 1 NO
Book 2 NO
Book 3 NO
*Record #2 in my Form
BORROWER NAME
Borrower 2
*sub form in a data sheet
ITEM NAME BORROWED SELECTED RETURNED
Book 4 NO
Book 5 NO
Book 6 NO
tblBorrower = BORROWER NAME
tblItem = ITEM NAME BORROWED
*Where should I use the third table "treltblborrowed"?
*Since book number 1,2,3 was borrowed, going to next record or creating
new
record, the ITEM NAME BORROWED SELECTED drop down list, only book 4,5 and
6
should show up since 1,2,3 still out and not yet returned. In that way we
can
also tell right away that book 1,2,3 is not available. Once the borrower
returned the item thats the only time that they will going to be available
again in the drop down list where borrower can borrowed same item over
time.
Thank you again Jeff. Sorry for confusing you, I don't have an expertice
on
explaining things I guest.
Jeff Boyce said:Perhaps I don't understand your specific situation well enough...
In my experience, a Borrower can borrow more than one Item. That make it
a
"one-to-many" relationship.
And over time, the same Item might be borrowed by more than one Borrower.
That makes the relationship between Borrower and Item a "many-to-many"
relationship, requiring a third table to resolve it (see my previous
email).
How is your situation different?
Regards
Jeff Boyce
Microsoft Office/Access MVP
g said:I don't have the last table. tblBorrower & trelItemBorrowed are the same
table.
:
I'm still not very clear on the table structure you are using...
If you have a well-normalized table structure, you'll probably have
three
tables:
tblBorrower (with information about folks who borrow)
tblItem (with information about items that get borrowed)
trelItemBorrowed (with information about who borrowed what and
when
and
when they returned it)
Does this match the table structure you are using?
Regards
Jeff Boyce
Microsoft Office/Access MVP
The form is bound into the tbl_Borrower. The combo box Item list is
bound
to
the column of tbl_Borrower.ItemList. All the item that borrowed will
recorded
to the tbl_Borrower.
:
If the item is borrowed, where is that fact recorded?
The generic approach you'd use is to modify the query that feeds
the
combobox to exclude any that are "borrowed" (how depends on how you
record
that fact).
Regards
Jeff Boyce
Microsoft Office/Access MVP
Thanks jeff.
My Combo box (Name: Item_List) in my form ( FormName:
FormBorrower)
row
source is from the table_ListItem (SELECT table_ListItem.[Title],
[table_ListItem.[Author], FROM table_ListItem;. What I wanted to
do
If
the
item with title say for example "The Book 1" was barrowed if you
create a
new record that Item should not show up anymore so it considered
not
available because it is still out not yet returned. So how to do
that?
Hope
this is more clear.
:
We aren't there. We can't see what you're working with. We
don't
know
how
your data is structured.
"How" depends on what you have.
More specific description may lead to more specific suggestions.
Regards
Jeff Boyce
Microsoft Office/Access MVP
Hello again,
I have a combo box for selecting item to be borrowed. I wanted
that
on
the
next record that item that been selected or borrowed should
not
show
up
anymore. How will I do this. Any Idea?
Thank you
Jeff Boyce said:This is JOPO (just one person's opinion)...
If I were setting up a database to track borrowers/items, I'd probably use:
tblBorrower
BorrowerID (Primary key)
FirstName
LastName
(... any other borrower-specific data)
tblItem
ItemID (Primary key)
ItemTitle
ItemDescription
(... any other item-specific data)
trelBorrowedItem
BorrowedItemID (Primary key)
BorrowerID (a foreign key pointing back to the borrower in
tblBorrower)
ItemID (a foreign key pointing back to the item in tblItem)
DateBorrowed
DateReturned
(... any other information specific to one person borrowing one
item)
I'd use a main form that displayed Borrower information, and a subform that
displays records from trelBorrowedItem.
The subform would use a combobox to list available Items (which you could
use a query to only show items that were available -- i.e., did not have an
"empty" DateReturned for the ItemID in the trelBorrowedItem.
Good Luck!
Regards
Jeff Boyce
Microsoft Office/Access MVP
g said:You're absolutely Correct. "Borrower can barrow more than one item"
Selecting the Item to be borrowed is from the combo box/drop down list in
data sheet.
By using your normalized table:
Ex:
ITEM NAME: BOOK1, BOOK2, BOOK3, BOOK4, BOOK5, AND BOOK6
*Record #1 in my Form
BORROWER NAME
Borrower 1
*sub form in a data sheet
ITEM NAME BORROWED SELECTED RETURNED
Book 1 NO
Book 2 NO
Book 3 NO
*Record #2 in my Form
BORROWER NAME
Borrower 2
*sub form in a data sheet
ITEM NAME BORROWED SELECTED RETURNED
Book 4 NO
Book 5 NO
Book 6 NO
tblBorrower = BORROWER NAME
tblItem = ITEM NAME BORROWED
*Where should I use the third table "treltblborrowed"?
*Since book number 1,2,3 was borrowed, going to next record or creating
new
record, the ITEM NAME BORROWED SELECTED drop down list, only book 4,5 and
6
should show up since 1,2,3 still out and not yet returned. In that way we
can
also tell right away that book 1,2,3 is not available. Once the borrower
returned the item thats the only time that they will going to be available
again in the drop down list where borrower can borrowed same item over
time.
Thank you again Jeff. Sorry for confusing you, I don't have an expertice
on
explaining things I guest.
Jeff Boyce said:Perhaps I don't understand your specific situation well enough...
In my experience, a Borrower can borrow more than one Item. That make it
a
"one-to-many" relationship.
And over time, the same Item might be borrowed by more than one Borrower.
That makes the relationship between Borrower and Item a "many-to-many"
relationship, requiring a third table to resolve it (see my previous
email).
How is your situation different?
Regards
Jeff Boyce
Microsoft Office/Access MVP
I don't have the last table. tblBorrower & trelItemBorrowed are the same
table.
:
I'm still not very clear on the table structure you are using...
If you have a well-normalized table structure, you'll probably have
three
tables:
tblBorrower (with information about folks who borrow)
tblItem (with information about items that get borrowed)
trelItemBorrowed (with information about who borrowed what and
when
and
when they returned it)
Does this match the table structure you are using?
Regards
Jeff Boyce
Microsoft Office/Access MVP
The form is bound into the tbl_Borrower. The combo box Item list is
bound
to
the column of tbl_Borrower.ItemList. All the item that borrowed will
recorded
to the tbl_Borrower.
:
If the item is borrowed, where is that fact recorded?
The generic approach you'd use is to modify the query that feeds
the
combobox to exclude any that are "borrowed" (how depends on how you
record
that fact).
Regards
Jeff Boyce
Microsoft Office/Access MVP
Thanks jeff.
My Combo box (Name: Item_List) in my form ( FormName:
FormBorrower)
row
source is from the table_ListItem (SELECT table_ListItem.[Title],
[table_ListItem.[Author], FROM table_ListItem;. What I wanted to
do
If
the
item with title say for example "The Book 1" was barrowed if you
create a
new record that Item should not show up anymore so it considered
not
available because it is still out not yet returned. So how to do
that?
Hope
this is more clear.
:
We aren't there. We can't see what you're working with. We
don't
know
how
your data is structured.
"How" depends on what you have.
More specific description may lead to more specific suggestions.
Regards
Jeff Boyce
Microsoft Office/Access MVP
Hello again,
I have a combo box for selecting item to be borrowed. I wanted
that
on
the
next record that item that been selected or borrowed should
not
show
up
anymore. How will I do this. Any Idea?
Thank you
Jeff Boyce said:This is JOPO (just one person's opinion)...
If I were setting up a database to track borrowers/items, I'd probably use:
tblBorrower
BorrowerID (Primary key)
FirstName
LastName
(... any other borrower-specific data)
tblItem
ItemID (Primary key)
ItemTitle
ItemDescription
(... any other item-specific data)
trelBorrowedItem
BorrowedItemID (Primary key)
BorrowerID (a foreign key pointing back to the borrower in
tblBorrower)
ItemID (a foreign key pointing back to the item in tblItem)
DateBorrowed
DateReturned
(... any other information specific to one person borrowing one
item)
I'd use a main form that displayed Borrower information, and a subform that
displays records from trelBorrowedItem.
The subform would use a combobox to list available Items (which you could
use a query to only show items that were available -- i.e., did not have an
"empty" DateReturned for the ItemID in the trelBorrowedItem.
Good Luck!
Regards
Jeff Boyce
Microsoft Office/Access MVP
g said:You're absolutely Correct. "Borrower can barrow more than one item"
Selecting the Item to be borrowed is from the combo box/drop down list in
data sheet.
By using your normalized table:
Ex:
ITEM NAME: BOOK1, BOOK2, BOOK3, BOOK4, BOOK5, AND BOOK6
*Record #1 in my Form
BORROWER NAME
Borrower 1
*sub form in a data sheet
ITEM NAME BORROWED SELECTED RETURNED
Book 1 NO
Book 2 NO
Book 3 NO
*Record #2 in my Form
BORROWER NAME
Borrower 2
*sub form in a data sheet
ITEM NAME BORROWED SELECTED RETURNED
Book 4 NO
Book 5 NO
Book 6 NO
tblBorrower = BORROWER NAME
tblItem = ITEM NAME BORROWED
*Where should I use the third table "treltblborrowed"?
*Since book number 1,2,3 was borrowed, going to next record or creating
new
record, the ITEM NAME BORROWED SELECTED drop down list, only book 4,5 and
6
should show up since 1,2,3 still out and not yet returned. In that way we
can
also tell right away that book 1,2,3 is not available. Once the borrower
returned the item thats the only time that they will going to be available
again in the drop down list where borrower can borrowed same item over
time.
Thank you again Jeff. Sorry for confusing you, I don't have an expertice
on
explaining things I guest.
Jeff Boyce said:Perhaps I don't understand your specific situation well enough...
In my experience, a Borrower can borrow more than one Item. That make it
a
"one-to-many" relationship.
And over time, the same Item might be borrowed by more than one Borrower.
That makes the relationship between Borrower and Item a "many-to-many"
relationship, requiring a third table to resolve it (see my previous
email).
How is your situation different?
Regards
Jeff Boyce
Microsoft Office/Access MVP
I don't have the last table. tblBorrower & trelItemBorrowed are the same
table.
:
I'm still not very clear on the table structure you are using...
If you have a well-normalized table structure, you'll probably have
three
tables:
tblBorrower (with information about folks who borrow)
tblItem (with information about items that get borrowed)
trelItemBorrowed (with information about who borrowed what and
when
and
when they returned it)
Does this match the table structure you are using?
Regards
Jeff Boyce
Microsoft Office/Access MVP
The form is bound into the tbl_Borrower. The combo box Item list is
bound
to
the column of tbl_Borrower.ItemList. All the item that borrowed will
recorded
to the tbl_Borrower.
:
If the item is borrowed, where is that fact recorded?
The generic approach you'd use is to modify the query that feeds
the
combobox to exclude any that are "borrowed" (how depends on how you
record
that fact).
Regards
Jeff Boyce
Microsoft Office/Access MVP
Thanks jeff.
My Combo box (Name: Item_List) in my form ( FormName:
FormBorrower)
row
source is from the table_ListItem (SELECT table_ListItem.[Title],
[table_ListItem.[Author], FROM table_ListItem;. What I wanted to
do
If
the
item with title say for example "The Book 1" was barrowed if you
create a
new record that Item should not show up anymore so it considered
not
available because it is still out not yet returned. So how to do
that?
Hope
this is more clear.
:
We aren't there. We can't see what you're working with. We
don't
know
how
your data is structured.
"How" depends on what you have.
More specific description may lead to more specific suggestions.
Regards
Jeff Boyce
Microsoft Office/Access MVP
Hello again,
I have a combo box for selecting item to be borrowed. I wanted
that
on
the
next record that item that been selected or borrowed should
not
show
up
anymore. How will I do this. Any Idea?
Thank you
g said:Hi I know I said I will end up here. I followed your suggestion for the 3
tables but when it comes to the drop down list since the row source is
base
on a query that will eliminate those item(s) borrowed using some
criteria(works fine), everytime that query refresh, the Item will not
going
to show up as well or will disappear in the form although the control
source
will go to the trelBorrowedItem table.
Hope your still willing to help me out here.
Jeff Boyce said:This is JOPO (just one person's opinion)...
If I were setting up a database to track borrowers/items, I'd probably
use:
tblBorrower
BorrowerID (Primary key)
FirstName
LastName
(... any other borrower-specific data)
tblItem
ItemID (Primary key)
ItemTitle
ItemDescription
(... any other item-specific data)
trelBorrowedItem
BorrowedItemID (Primary key)
BorrowerID (a foreign key pointing back to the borrower in
tblBorrower)
ItemID (a foreign key pointing back to the item in tblItem)
DateBorrowed
DateReturned
(... any other information specific to one person borrowing one
item)
I'd use a main form that displayed Borrower information, and a subform
that
displays records from trelBorrowedItem.
The subform would use a combobox to list available Items (which you could
use a query to only show items that were available -- i.e., did not have
an
"empty" DateReturned for the ItemID in the trelBorrowedItem.
Good Luck!
Regards
Jeff Boyce
Microsoft Office/Access MVP
g said:You're absolutely Correct. "Borrower can barrow more than one item"
Selecting the Item to be borrowed is from the combo box/drop down list
in
data sheet.
By using your normalized table:
Ex:
ITEM NAME: BOOK1, BOOK2, BOOK3, BOOK4, BOOK5, AND BOOK6
*Record #1 in my Form
BORROWER NAME
Borrower 1
*sub form in a data sheet
ITEM NAME BORROWED SELECTED RETURNED
Book 1 NO
Book 2 NO
Book 3 NO
*Record #2 in my Form
BORROWER NAME
Borrower 2
*sub form in a data sheet
ITEM NAME BORROWED SELECTED RETURNED
Book 4 NO
Book 5 NO
Book 6 NO
tblBorrower = BORROWER NAME
tblItem = ITEM NAME BORROWED
*Where should I use the third table "treltblborrowed"?
*Since book number 1,2,3 was borrowed, going to next record or creating
new
record, the ITEM NAME BORROWED SELECTED drop down list, only book 4,5
and
6
should show up since 1,2,3 still out and not yet returned. In that way
we
can
also tell right away that book 1,2,3 is not available. Once the
borrower
returned the item thats the only time that they will going to be
available
again in the drop down list where borrower can borrowed same item over
time.
Thank you again Jeff. Sorry for confusing you, I don't have an
expertice
on
explaining things I guest.
:
Perhaps I don't understand your specific situation well enough...
In my experience, a Borrower can borrow more than one Item. That make
it
a
"one-to-many" relationship.
And over time, the same Item might be borrowed by more than one
Borrower.
That makes the relationship between Borrower and Item a "many-to-many"
relationship, requiring a third table to resolve it (see my previous
email).
How is your situation different?
Regards
Jeff Boyce
Microsoft Office/Access MVP
I don't have the last table. tblBorrower & trelItemBorrowed are the
same
table.
:
I'm still not very clear on the table structure you are using...
If you have a well-normalized table structure, you'll probably have
three
tables:
tblBorrower (with information about folks who borrow)
tblItem (with information about items that get borrowed)
trelItemBorrowed (with information about who borrowed what and
when
and
when they returned it)
Does this match the table structure you are using?
Regards
Jeff Boyce
Microsoft Office/Access MVP
The form is bound into the tbl_Borrower. The combo box Item list
is
bound
to
the column of tbl_Borrower.ItemList. All the item that borrowed
will
recorded
to the tbl_Borrower.
:
If the item is borrowed, where is that fact recorded?
The generic approach you'd use is to modify the query that feeds
the
combobox to exclude any that are "borrowed" (how depends on how
you
record
that fact).
Regards
Jeff Boyce
Microsoft Office/Access MVP
Thanks jeff.
My Combo box (Name: Item_List) in my form ( FormName:
FormBorrower)
row
source is from the table_ListItem (SELECT
table_ListItem.[Title],
[table_ListItem.[Author], FROM table_ListItem;. What I wanted
to
do
If
the
item with title say for example "The Book 1" was barrowed if
you
create a
new record that Item should not show up anymore so it
considered
not
available because it is still out not yet returned. So how to
do
that?
Hope
this is more clear.
:
We aren't there. We can't see what you're working with. We
don't
know
how
your data is structured.
"How" depends on what you have.
More specific description may lead to more specific
suggestions.
Regards
Jeff Boyce
Microsoft Office/Access MVP
Hello again,
I have a combo box for selecting item to be borrowed. I
wanted
that
on
the
next record that item that been selected or borrowed should
not
show
up
anymore. How will I do this. Any Idea?
Thank you
Jeff Boyce said:I'm having a little trouble visualizing what's happening.
Is there a chance your subform is a "continuous form" rather than a "single
form"?
You didn't indicate how (i.e., the SQL statement) you are eliminating
borrowed items for that combobox.
Regards
Jeff Boyce
Microsoft Office/Access MVP
g said:Hi I know I said I will end up here. I followed your suggestion for the 3
tables but when it comes to the drop down list since the row source is
base
on a query that will eliminate those item(s) borrowed using some
criteria(works fine), everytime that query refresh, the Item will not
going
to show up as well or will disappear in the form although the control
source
will go to the trelBorrowedItem table.
Hope your still willing to help me out here.
Jeff Boyce said:This is JOPO (just one person's opinion)...
If I were setting up a database to track borrowers/items, I'd probably
use:
tblBorrower
BorrowerID (Primary key)
FirstName
LastName
(... any other borrower-specific data)
tblItem
ItemID (Primary key)
ItemTitle
ItemDescription
(... any other item-specific data)
trelBorrowedItem
BorrowedItemID (Primary key)
BorrowerID (a foreign key pointing back to the borrower in
tblBorrower)
ItemID (a foreign key pointing back to the item in tblItem)
DateBorrowed
DateReturned
(... any other information specific to one person borrowing one
item)
I'd use a main form that displayed Borrower information, and a subform
that
displays records from trelBorrowedItem.
The subform would use a combobox to list available Items (which you could
use a query to only show items that were available -- i.e., did not have
an
"empty" DateReturned for the ItemID in the trelBorrowedItem.
Good Luck!
Regards
Jeff Boyce
Microsoft Office/Access MVP
You're absolutely Correct. "Borrower can barrow more than one item"
Selecting the Item to be borrowed is from the combo box/drop down list
in
data sheet.
By using your normalized table:
Ex:
ITEM NAME: BOOK1, BOOK2, BOOK3, BOOK4, BOOK5, AND BOOK6
*Record #1 in my Form
BORROWER NAME
Borrower 1
*sub form in a data sheet
ITEM NAME BORROWED SELECTED RETURNED
Book 1 NO
Book 2 NO
Book 3 NO
*Record #2 in my Form
BORROWER NAME
Borrower 2
*sub form in a data sheet
ITEM NAME BORROWED SELECTED RETURNED
Book 4 NO
Book 5 NO
Book 6 NO
tblBorrower = BORROWER NAME
tblItem = ITEM NAME BORROWED
*Where should I use the third table "treltblborrowed"?
*Since book number 1,2,3 was borrowed, going to next record or creating
new
record, the ITEM NAME BORROWED SELECTED drop down list, only book 4,5
and
6
should show up since 1,2,3 still out and not yet returned. In that way
we
can
also tell right away that book 1,2,3 is not available. Once the
borrower
returned the item thats the only time that they will going to be
available
again in the drop down list where borrower can borrowed same item over
time.
Thank you again Jeff. Sorry for confusing you, I don't have an
expertice
on
explaining things I guest.
:
Perhaps I don't understand your specific situation well enough...
In my experience, a Borrower can borrow more than one Item. That make
it
a
"one-to-many" relationship.
And over time, the same Item might be borrowed by more than one
Borrower.
That makes the relationship between Borrower and Item a "many-to-many"
relationship, requiring a third table to resolve it (see my previous
email).
How is your situation different?
Regards
Jeff Boyce
Microsoft Office/Access MVP
I don't have the last table. tblBorrower & trelItemBorrowed are the
same
table.
:
I'm still not very clear on the table structure you are using...
If you have a well-normalized table structure, you'll probably have
three
tables:
tblBorrower (with information about folks who borrow)
tblItem (with information about items that get borrowed)
trelItemBorrowed (with information about who borrowed what and
when
and
when they returned it)
Does this match the table structure you are using?
Regards
Jeff Boyce
Microsoft Office/Access MVP
The form is bound into the tbl_Borrower. The combo box Item list
is
bound
to
the column of tbl_Borrower.ItemList. All the item that borrowed
will
recorded
to the tbl_Borrower.
:
If the item is borrowed, where is that fact recorded?
The generic approach you'd use is to modify the query that feeds
the
combobox to exclude any that are "borrowed" (how depends on how
you
record
that fact).
Regards
Jeff Boyce
Microsoft Office/Access MVP
Thanks jeff.
My Combo box (Name: Item_List) in my form ( FormName:
FormBorrower)
row
source is from the table_ListItem (SELECT
table_ListItem.[Title],
[table_ListItem.[Author], FROM table_ListItem;. What I wanted
to
do
If
the
item with title say for example "The Book 1" was barrowed if
you
create a
new record that Item should not show up anymore so it
considered
not
available because it is still out not yet returned. So how to
do
that?
Hope
this is more clear.
:
We aren't there. We can't see what you're working with. We
don't
know
how
your data is structured.
"How" depends on what you have.
More specific description may lead to more specific
suggestions.
Regards
Jeff Boyce
Microsoft Office/Access MVP
Hello again,
I have a combo box for selecting item to be borrowed. I
wanted
that
on
the
next record that item that been selected or borrowed should
not
show
up
anymore. How will I do this. Any Idea?
Thank you
g said:My subform is in datasheet format: I guest it doesn't matter if it is
single,
continuous or datasheet.
* This is the row source of the Item field in the subform
SELECT tblItem.ItemID, tblItem.Title FROM tblItem LEFT JOIN
trelBorrowedItem
ON tblItem.ItemID = trelBorrowedItem.ItemID WHERE
(((trelBorrowedItem.ItemID)
Is Null));
* Control source is in trelBorrowedItem.ItemID
The item borrowed stored in the table but my problem is in the form the
item
selected will disappear since the query or the row source will refresh
again.
Jeff Boyce said:I'm having a little trouble visualizing what's happening.
Is there a chance your subform is a "continuous form" rather than a
"single
form"?
You didn't indicate how (i.e., the SQL statement) you are eliminating
borrowed items for that combobox.
Regards
Jeff Boyce
Microsoft Office/Access MVP
g said:Hi I know I said I will end up here. I followed your suggestion for the
3
tables but when it comes to the drop down list since the row source is
base
on a query that will eliminate those item(s) borrowed using some
criteria(works fine), everytime that query refresh, the Item will not
going
to show up as well or will disappear in the form although the control
source
will go to the trelBorrowedItem table.
Hope your still willing to help me out here.
:
This is JOPO (just one person's opinion)...
If I were setting up a database to track borrowers/items, I'd probably
use:
tblBorrower
BorrowerID (Primary key)
FirstName
LastName
(... any other borrower-specific data)
tblItem
ItemID (Primary key)
ItemTitle
ItemDescription
(... any other item-specific data)
trelBorrowedItem
BorrowedItemID (Primary key)
BorrowerID (a foreign key pointing back to the borrower in
tblBorrower)
ItemID (a foreign key pointing back to the item in tblItem)
DateBorrowed
DateReturned
(... any other information specific to one person borrowing
one
item)
I'd use a main form that displayed Borrower information, and a subform
that
displays records from trelBorrowedItem.
The subform would use a combobox to list available Items (which you
could
use a query to only show items that were available -- i.e., did not
have
an
"empty" DateReturned for the ItemID in the trelBorrowedItem.
Good Luck!
Regards
Jeff Boyce
Microsoft Office/Access MVP
You're absolutely Correct. "Borrower can barrow more than one item"
Selecting the Item to be borrowed is from the combo box/drop down
list
in
data sheet.
By using your normalized table:
Ex:
ITEM NAME: BOOK1, BOOK2, BOOK3, BOOK4, BOOK5, AND BOOK6
*Record #1 in my Form
BORROWER NAME
Borrower 1
*sub form in a data sheet
ITEM NAME BORROWED SELECTED RETURNED
Book 1 NO
Book 2 NO
Book 3 NO
*Record #2 in my Form
BORROWER NAME
Borrower 2
*sub form in a data sheet
ITEM NAME BORROWED SELECTED RETURNED
Book 4 NO
Book 5 NO
Book 6 NO
tblBorrower = BORROWER NAME
tblItem = ITEM NAME BORROWED
*Where should I use the third table "treltblborrowed"?
*Since book number 1,2,3 was borrowed, going to next record or
creating
new
record, the ITEM NAME BORROWED SELECTED drop down list, only book
4,5
and
6
should show up since 1,2,3 still out and not yet returned. In that
way
we
can
also tell right away that book 1,2,3 is not available. Once the
borrower
returned the item thats the only time that they will going to be
available
again in the drop down list where borrower can borrowed same item
over
time.
Thank you again Jeff. Sorry for confusing you, I don't have an
expertice
on
explaining things I guest.
:
Perhaps I don't understand your specific situation well enough...
In my experience, a Borrower can borrow more than one Item. That
make
it
a
"one-to-many" relationship.
And over time, the same Item might be borrowed by more than one
Borrower.
That makes the relationship between Borrower and Item a
"many-to-many"
relationship, requiring a third table to resolve it (see my
previous
email).
How is your situation different?
Regards
Jeff Boyce
Microsoft Office/Access MVP
I don't have the last table. tblBorrower & trelItemBorrowed are
the
same
table.
:
I'm still not very clear on the table structure you are using...
If you have a well-normalized table structure, you'll probably
have
three
tables:
tblBorrower (with information about folks who borrow)
tblItem (with information about items that get borrowed)
trelItemBorrowed (with information about who borrowed what
and
when
and
when they returned it)
Does this match the table structure you are using?
Regards
Jeff Boyce
Microsoft Office/Access MVP
The form is bound into the tbl_Borrower. The combo box Item
list
is
bound
to
the column of tbl_Borrower.ItemList. All the item that
borrowed
will
recorded
to the tbl_Borrower.
:
If the item is borrowed, where is that fact recorded?
The generic approach you'd use is to modify the query that
feeds
the
combobox to exclude any that are "borrowed" (how depends on
how
you
record
that fact).
Regards
Jeff Boyce
Microsoft Office/Access MVP
Thanks jeff.
My Combo box (Name: Item_List) in my form ( FormName:
FormBorrower)
row
source is from the table_ListItem (SELECT
table_ListItem.[Title],
[table_ListItem.[Author], FROM table_ListItem;. What I
wanted
to
do
If
the
item with title say for example "The Book 1" was barrowed
if
you
create a
new record that Item should not show up anymore so it
considered
not
available because it is still out not yet returned. So how
to
do
that?
Hope
this is more clear.
:
We aren't there. We can't see what you're working with.
We
don't
know
how
your data is structured.
"How" depends on what you have.
More specific description may lead to more specific
suggestions.
Regards
Jeff Boyce
Microsoft Office/Access MVP
Hello again,
I have a combo box for selecting item to be borrowed. I
wanted
that
on
the
next record that item that been selected or borrowed
should
not
show
up
anymore. How will I do this. Any Idea?
Thank you