need help, textbox auto update

  • Thread starter Thread starter ping
  • Start date Start date
P

ping

i have 2 tables; tblOrdersDetails and tblToolsDetails.

In tblToolsDetails, i have a field named ToolId which is
the primary key, and another field called ToolsDescript.

In tblOrderDetails, i have a field named ToolID which is
the foreign key. its indexed property set to yes
(duplicates ok) by default.

in a form, i key in ToolID(tblOrderDetails) and the next
txtbox whose source is from ToolsDescript field of
tblToolsDetails will automatically fill in. works fine.

Problem comes, if i set the indexed property of the
foreign key to yes(no duplicates) and when i try to use
the form again, the textbox will not auto fill in.

how can i get around this?
 
If you set the Foreign Key not to accept duplicates, that means you will not
be able to place more than one order for the same tool. Is that what you
want? I suspect that this may be at the source of the problem...
 
this is used in part of a loaning out of tools database.
ToolsID from the tblOrderDetails is used to allocate a
certain tool to a person from a fixed list of ToolsID in
tblToolsDetails.
basically a person cant loan out a tool which has already
been loaned out, therefore i need it to be no duplicates.
-----Original Message-----
Hi,
A foreign key field should, by it's nature (one to many) , allow duplicates.
Why do you want it to be unique?

--
HTH
Dan Artuso, Access MVP


"ping" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
Hi,
A foreign key field should, by it's nature (one to many) , allow duplicates.
Why do you want it to be unique?
 
Hi,
I don't think that is what you want.
What do you do when the tool is returned?
Delete the entry?
I would think you would want to keep a record of the loans.

You must allow duplicates but you should have a boolean field to indicate
that the tool is either available or out on loan (or perhaps a DateReturned field, which
you definitely should have in your table).

You would then use either DLookup() or a SQL statement to check the status
of a tool and react accordingly.
 
actually, before your suggestion i did intend to delete
the entry after the tool was returned. i dunt need to keep
a record of the transactions although having a record
would be a plus.
i was trying to keep it simple as i have no knowledge of
programming. if i were to inplement boolean field,

is my flow correct?
i put the boolean field in tblToolsDetails?
and when the user keys in the toolID, i have to put some
if/else code in the before_update event of the toolID
control to check up the boolean field, if its loaned out,i
have to cancel = true to prevent the update.
-----Original Message-----
Hi,
I don't think that is what you want.
What do you do when the tool is returned?
Delete the entry?
I would think you would want to keep a record of the loans.

You must allow duplicates but you should have a boolean field to indicate
that the tool is either available or out on loan (or
Click to expand...
perhaps a DateReturned field, which
 
Hi,
I would personally go with a DateReturned field but if you're using a boolean field
you could check like this:

If DCount("[toolId]","tblToolsDetails","yourBoolField = -1 And toolId =" & Me.yourToolId) <> 0 Then
MsgBox "that tool is presently on loan"
'cancel your save
End If

You have to substitute the correct names for your boolean field and for the control
on the form that displays the toolId
 
Hi

I would go for what Dan Artuso is suggesting and allow duplicate entries in
the foreign key field. Include a field to indicate whether or not the tool is
currently on loan (a Yes/No field or a Date Returned field would work), then
include a criterion in the query underlying the combo box the user has to
select the tool, so that the tool does not appear on the list if it is
currently on loan. (i.e. the query will only return tools that are not
currently on loan).

If you need further help with this, feel free to email me on david dot
cleave at logistics dot nhs dot uk.

Cheers

David
 
alright thanks for your help :)

David Cleave said:
Hi

I would go for what Dan Artuso is suggesting and allow duplicate entries in
the foreign key field. Include a field to indicate whether or not the tool is
currently on loan (a Yes/No field or a Date Returned field would work), then
include a criterion in the query underlying the combo box the user has to
select the tool, so that the tool does not appear on the list if it is
currently on loan. (i.e. the query will only return tools that are not
currently on loan).

If you need further help with this, feel free to email me on david dot
cleave at logistics dot nhs dot uk.

Cheers

David
Click to expand...
 
thank you for your help :)

Dan Artuso said:
Hi,
I would personally go with a DateReturned field but if you're using a boolean field
you could check like this:

If DCount("[toolId]","tblToolsDetails","yourBoolField = -1 And toolId =" & Me.yourToolId) <> 0 Then
MsgBox "that tool is presently on loan"
'cancel your save
End If

You have to substitute the correct names for your boolean field and for the control
on the form that displays the toolId
--
HTH
Dan Artuso, Access MVP


ping said:
actually, before your suggestion i did intend to delete
the entry after the tool was returned. i dunt need to keep
a record of the transactions although having a record
would be a plus.
i was trying to keep it simple as i have no knowledge of
programming. if i were to inplement boolean field,

is my flow correct?
i put the boolean field in tblToolsDetails?
and when the user keys in the toolID, i have to put some
if/else code in the before_update event of the toolID
control to check up the boolean field, if its loaned out,i
have to cancel = true to prevent the update.

perhaps a DateReturned field, which
Click to expand...
Click to expand...
 
ok thanks

David Cleave said:
Hi

I would go for what Dan Artuso is suggesting and allow duplicate entries in
the foreign key field. Include a field to indicate whether or not the tool is
currently on loan (a Yes/No field or a Date Returned field would work), then
include a criterion in the query underlying the combo box the user has to
select the tool, so that the tool does not appear on the list if it is
currently on loan. (i.e. the query will only return tools that are not
currently on loan).

If you need further help with this, feel free to email me on david dot
cleave at logistics dot nhs dot uk.

Cheers

David
Click to expand...
 
Back
Top