CODEWITHSUNDEEP
ms-access
Edmond
Edmond

Reputation: 149

MS Access 03 Query using Yes/No Data Type

I have a table in Access..
AccessKey
AccessCardID
Distributed (yes/no)

On my form is a combo box with 10 Access Cards. I need help setting up a query where if guest A gets Access Card #1, the user will only be able to choose from Access Cards #2-#10, for guest B, c, and so on, until guest A returns Access Card #1. so far the the query i have is 

SELECT AccessCardID  
FROM AccessKey  
WHERE Distributed = False;

Here is my new code for After update

Private Sub AccessKeyNo_AfterUpdate()
If MsgBox("Do you want to assign Access Key" & Me.AccessKeyNo & "?", _
        vbYesNo) = vbYes Then
    Me.GuestAccessKeyID = Me.AccessKeyNo
    Me.MyCheckBox = Not IsNull(Me.GuestAccessKeyID)
    Me.AccessKeyNo.Requery
End If
End Sub

My new query

SELECT AccessKey.AccessKeyID
FROM AccessKey LEFT JOIN Guest ON AccessKey.AccessKeyID=Guest.GuestAccessKeyID
WHERE (((Guest.GuestAccessKeyID) Is Null));

And the On current for the form

Private Sub Form_Current()
Me.MyCheckBox = Not IsNull(Me.GuestAccessKeyID)

If IsNull(Me![GuestID]) Then
DoCmd.GoToControl "GuestFirstName"
End If
End Sub

Upvotes: 0

Views: 1403

Answers (3)

Fionnuala
Fionnuala

Reputation: 91376

If you do not like writing code, you can use a subform and set the record source to your table, AccessKey. Alternatively you can write a little code, say:

Private Sub NameOfComboHere_AfterUpdate()
Dim db As Database
Dim strSQL As String

Set db = CurrentDb
    If MsgBox("Do you want to assign " & Me.NameOfComboHere & "?", _
            vbYesNo) = vbYes Then
        strSQL = "UPDATE AccessKey SET Distributed = True " _
            & "WHERE AccessCardID = " & Me.NameOfComboHere
        db.Execute strSQL, dbFailOnError
        If db.RecordsAffected = 1 Then
            MsgBox Me.NameOfComboHere & " has been assigned."
        End If
    End If

    Me.NameOfComboHere.Requery
End Sub

There are a few notes. This assumes that the combobox has a bound column of AccessCardID and that AccessCardID is numeric. It also assumes that you have a reference to Microsoft DAO 3.x Object library. A subform may be the best bet.

EDIT based on Comments

Let us say you have a card id in the guest table, first, add a little code to the Current event for the form:

Me.MyCheckBox = Not IsNull(Me.GuestAccessCard)

This will set the checkbox to false if there is no card id and to true if there is an id.

You will need to change the query for the combobox to:

SELECT AccessCardID 
FROM AccessKey 
LEFT JOIN Guests 
ON AccessKey.AccessCardID = Guest.GuestAccessCard
WHERE Guest.GuestAccessCard Is Null

Then the After Update event would run:

Private Sub NameOfComboHere_AfterUpdate()
    If MsgBox("Do you want to assign " & Me.NameOfComboHere & "?", _
            vbYesNo) = vbYes Then
        Me.GuestAccessCard = Me.NameOfComboHere
        Me.MyCheckBox = Not IsNull(Me.GuestAccessCard)
        ''The data will have to be saved for the
        ''combo to update with the new data
        If Me.Dirty Then Me.Dirty = False
        Me.NameOfComboHere.Requery
    End If

End Sub

And do not forget to set the GuestAccessCard to Null when the checkbox is unticked and requery the combo.

Upvotes: 0

David-W-Fenton
David-W-Fenton

Reputation: 23067

I may be completely misunderstanding the situation here, but it seems to me that you don't want to have a Boolean field in the table with your cards to indicate if it's checked out -- all you need is a field in your Guests table where you enter the card number. Then you can tell which cards are available with a left join against the card numbers that are in use:

  SELECT Cards.CardNumber
  FROM Cards LEFT JOIN Guests ON Cards.CardNumber = Guests.CardNumber
  WHERE Guests.CardNumber Is Null

This would mean that there's only ever one place where the information is stored (in the Guests table). You could also put a unique index on the CardNumber field in the Guests table (allow Nulls) so you could never assign the same card to two guests.

This may be the way you are doing things, or the way Remou has suggested, but I got too confused by all the convoluted back and forth!

Upvotes: 0

David McEwing
David McEwing

Reputation: 3340

Why don't you set Distributed to False in the above query so that you get the list of cards which have not been distributed yet.

Upvotes: 1

Related Questions