Reject user making same selection twice

[oxicottin]

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

 

[1DMF]

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

 

[MajP]

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.

 

[1DMF]

I got the impression it was the one control on a continuous form so multiple records can't have the same value in a particular column?

"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

 

[oxicottin]

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

 

[MajP]

Yes. Something like


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

 

[MajP]

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.

 

[MajP]

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

 

[oxicottin]

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

 

[dhookom]

The ORDER BY must follow the WHERE clause.

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

 

[oxicottin]

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

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

Thanks,
SoggyCashew.....

 

[MajP]

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.

 

[oxicottin]

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

 

[MajP]

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

 

[oxicottin]

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

 

[MajP]

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

 

[oxicottin]

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

Thanks,
SoggyCashew.....

 

[oxicottin]

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

 

[MajP]

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]

 

[Andrzejek]

Sometimes I am surprised how much unnecessary coding people have.

Would it be enough to say:
[tt]
SELECT FunctionID, FunctionSeqNum, Requirements
FROM qry_Requirements
WHERE FunctionID NOT IN (Whatever was selected before)
ORDER BY FunctionSeqNum
[/tt]

Without repeating [tt]qry_Requirements[/tt] over and over...

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.