INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Reject user making same selection twice

Reject user making same selection twice

(OP)
Hello I have a combo box [cboRejectLotNumber] on a continious form [fsub_RejectedData] and I cant have the same selection used more than once. What VBA can I add that will stop the user and give a message and send them back to the combo box? Thanks!

Thanks,
SoggyCashew.....

RE: Reject user making same selection twice

You could use the change event to disable/lock all the other controls.

But might not be helpful if they want to change their selection.

You could come up with an edit option to re-enable the controls to allow edit.

Or perhaps you could use the change event to clone the record set and analyse the selections.


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

RE: Reject user making same selection twice

If I understand correctly if the list has cat, dog, mouse and the first record selects cat, the combo should then only show dog and mouse. You can do this with the SQL for the combobox. look at the NOT IN clause of SQL.

RE: Reject user making same selection twice

(OP)
MaJP, are you meaning add the NOT IN to the combo box's row source?

SELECT DISTINCT [qry_DependantLotNumbers].LotNumber FROM qry_DependantLotNumbers ORDER BY [LotNumber];

Thanks,
SoggyCashew.....

RE: Reject user making same selection twice

Yes. Something like


SELECT DISTINCT [qry_DependantLotNumbers].LotNumber FROM qry_DependantLotNumbers ORDER BY [LotNumber] where LotNumber NOT IN (Select LotNumber_FK from YourTableUsedInTheForm)

RE: Reject user making same selection twice

You could write vba on the before update event to disallow a selection, but I hate those designs. Do not provide me a choice and then afterwards tell me that I cannot pick that choice. Instead show me only the viable choices.

RE: Reject user making same selection twice

Also if you use the query, you need to make sure to requery the combo on the forms oncurrent event.

RE: Reject user making same selection twice

(OP)
Im getting an error,

Syntax error (missing operator) in a query expression '[LotNumber] where LotLumber NOT IN Select LotNumber_HoldID from tblsub_RejectedHoldData)'.


SELECT DISTINCT [qry_DependantLotNumbers].LotNumber FROM qry_DependantLotNumbers ORDER BY [LotNumber] where LotNumber NOT IN (Select LotNumber_HoldID from tblsub_RejectedHoldData)

Thanks,
SoggyCashew.....

RE: Reject user making same selection twice

The ORDER BY must follow the WHERE clause.

CODE --> SQL

SELECT DISTINCT [qry_DependantLotNumbers].LotNumber FROM qry_DependantLotNumbers 
where LotNumber NOT IN (Select LotNumber_HoldID from tblsub_RejectedHoldData) 
ORDER BY [LotNumber] 

Duane
Hook'D on Access
MS Access MVP

RE: Reject user making same selection twice

(OP)
Ok I finally got it to work with the SQL below now I cant get the data to refresh. This is a subform and I enter numbers into my main form into the "LotNumber" then i move to the subform and make a selection into my cboRejectLotNumber "RejectLotNumber".

CODE --> VBA

SELECT DISTINCT [qry_DependantLotNumbers].LotNumber FROM qry_DependantLotNumbers WHERE LotNumber 
NOT IN (SELECT tblsub_RejectedHoldData.RejectLotNumber FROM tblsub_RejectedHoldData) ORDER BY [LotNumber]; 

Thanks,
SoggyCashew.....

RE: Reject user making same selection twice

Quote:

This is a subform and I enter numbers into my main form into the "LotNumber" then i move to the subform and make a selection into my cboRejectLotNumber "RejectLotNumber".
Sorry I cannot help because I have no idea what this is saying.

RE: Reject user making same selection twice

(OP)
OK, I enter numbers into a textbox called txtLotNumber [LotNumber] on my main form then I choose the LotNumber which I now call cboRejectLotNumber [RejectLotNumber] on my subfom and it doesnt update the combo box after making a selection. In your previous post you sugestied using the OnCurrent event since im using a query so I tried just adding me.cboRejectLotNumber.requery but I still get the full list unless I close the form and reopen then the selections I made are gone.

Thanks,
SoggyCashew.....

RE: Reject user making same selection twice

Try instead to use the on enter on the combobox. Each time you enter the combobox it will then requery.

RE: Reject user making same selection twice

(OP)
MajP, requerying the combo box OnEnter worked thank you. Just out of curosity if I was to use VBA instead and I know you said you hate it but im wanting to know how I could do this both ways. How can I now show the list and if the user selects a "Used number" it would give them a message.

Thanks,

Thanks,
SoggyCashew.....

RE: Reject user making same selection twice

CODE -->

Private Sub cboStatus_BeforeUpdate(Cancel As Integer)
  If IsAlreadySelected(Me.cboStatus) Then
    MsgBox "The Choice " & Me.cboStatus.Value & " has aleady been used. Please choose another choice."
    Cancel = True
    cboStatus.Undo
  End If
End Sub

Public Function IsAlreadySelected(TheComboBox As Access.ComboBox) As Boolean
  Dim RS As DAO.Recordset
  Dim boundField As String
  Set RS = Me.RecordsetClone
  boundField = TheComboBox.ControlSource
  RS.FindFirst boundField & " = '" & TheComboBox & "'"
  IsAlreadySelected = Not RS.NoMatch
End Function 

RE: Reject user making same selection twice

(OP)
Thanks You MajP! I dont like the idea of providing a choice and then saying that choice isnt avalible choose again either!

Thanks,
SoggyCashew.....

RE: Reject user making same selection twice

(OP)
Majp, I have another combobox I need to show only the data that hasnt been used and I cant get it to work. If I use a FunctionSeqNum then remove it from the list. I tried and I cant even get data...

SELECT qry_Requirements.FunctionID, qry_Requirements.FunctionSeqNum, qry_Requirements.Requirements FROM qry_Requirements ORDER BY qry_Requirements.[FunctionSeqNum];

Thanks,
SoggyCashew.....

RE: Reject user making same selection twice

How do you determine what is used? In other words you are storing values in some other table and field. I assume you are storing a function ID.
Probably something like
SELECT qry_Requirements.FunctionID, qry_Requirements.FunctionSeqNum, qry_Requirements.Requirements FROM qry_Requirements
where FunctionID NOT IN (Select functionID from someothertable)
ORDER BY qry_Requirements.[FunctionSeqNum]

RE: Reject user making same selection twice

Sometimes I am surprised how much unnecessary coding people have.

Would it be enough to say:

SELECT FunctionID, FunctionSeqNum, Requirements
FROM qry_Requirements
WHERE FunctionID NOT IN (Whatever was selected before)
ORDER BY FunctionSeqNum


Without repeating qry_Requirements over and over... smile

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Reject user making same selection twice

(OP)
Thanks Majp, I figured it out when you said "storing values in some other table and field" and I realized I wasnt. Also thanks Andrzejek for showing us that we can remove text and it will still work...

SELECT FunctionID, RequirementID, FunctionSeqNum, Requirements FROM qry_Requirements
where RequirementID NOT IN (Select RequirementID from tbl_FunctionRequirementsOutput)
ORDER BY FunctionSeqNum;

Thanks,
SoggyCashew.....

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close