Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Combo Box with Filtered Numbers (2007 SP1)

    Hi again

    I'm having trouble coming up with a way to get Access to 'release' a number back to me whenever it becomes available . . .

    I've created a Database that stores documents in 'boxes' (like pigeon holes). There is a finite number of boxes available, and I need to find a way for Access to show these via a Combo Box whenever they are available.

    Originally, I created a new Table called BoxNumbers and populated it with (eg) 1-10. I added this via a Combo Box to the Box Form——this Form contains both a Field to store this Number and a Date when the Documents in the Box are sent away.

    What I wanted to do was to create a Query that looked at the Box Number and the Date Sent. If the Sent Date Sent was empty, then that means the number is taken. If the Sent Date field is populated, then the Number should become available again, as the documents have left the building, so the box is now empty.

    I created a Query with a Join and asked Access to display ALL Records in the BoxNumbers Table and only those Records in the other Table where Joined Table where equal (with Date Sent criteria of Is Not Null, but it just displays the BoxNumbers where BOTH Fields are Equal.

    I'm sure I'm doing this wrong, but I'm melting my brain trying to figure out how to implement this.

    I hope I've made my question clear!

    Thanks in advance for any help or advice.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box with Filtered Numbers (2007 SP1)

    Do you need to need to know the box number AFTER the document has been sent? If not, I'd clear the box number in the documents table when the sent date is filled in. You can do this in the After Update event of the Sent Date control on the form.

    Private Sub SentDate_AfterUpdate()
    If Not IsNull(Me.SentDate) Then
    Me.cboBoxNumber = Null
    End If
    End Sub

    (This is air code, of course. You'll have to substitute the correct names.)

    You can then create a query based on the BoxNumbers table and the documents table with a left join on the BoxNumber, and set the criteria for the BoxNumber field in the documents table to Is Null.
    Alternatively, create a query based on the BoxNumbers table alone, with the following condition on the BoxNumber field:

    Not In (SELECT BoxNumber FROM documentstable)

    (Again, you'll have to substitute the correct names.)

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Combo Box with Filtered Numbers (2007 SP1)

    Hi Hans

    In answer to your question, yes, they would need to refer back to the Record to see what Box Number the documents were stored in, so the 2nd option you supplied would be best.

    I have created a Table and populated it with numbers. I called this Table "tblSRN".
    I added a Combo Box to the frmUndertakings subform that pulls the numbers from the above table and then stores that value in the tblUndertakings field called "SRN"
    I then modified the code of the Combo Box so that it matched your code below (replacing the names so that it was Not In (SELECT SRN FROM tblUndertakings) , but I get the following error:
    Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

    So, I tried just adding the code to the Criteria of the Query, so that it looked like this:
    SELECT tblSRN.SRN
    FROM tblSRN
    WHERE (((tblSRN.SRN) Not In (SELECT SRN FROM tblUndertakings)));

    The code runs, but the combo box is empty.

    I've never used the "Not In" before, so I'm not sure what I'm doing. Would you be able to guide me a little, please?

    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box with Filtered Numbers (2007 SP1)

    If

    SELECT tblSRN.SRN
    FROM tblSRN
    WHERE (((tblSRN.SRN) Not In (SELECT SRN FROM tblUndertakings)));

    returns no records, it means that all SRN numbers in tblSRN are used in tblUndertakings.

  5. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Combo Box with Filtered Numbers (2007 SP1)

    I've checked both the SRN Table and the Undertakings Table, and there are still 7 available numbers (the database hasn't begun to be used yet, so there are only some dummy records in).

    At the moment, there are only 4 Records in the tblUndertakings Table, and the SRN Field is only populated with 3 numbers: 7, 1, and 3.

    The SRN Table used in the combo box has 10 Records available, so 2,4,5,6,8,9, and 10 should all be free?

    On a separate note, I'm unclear how these numbers will be 'released' when the DateSent field becomes populated?

    As always, thanks for your patience and help.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box with Filtered Numbers (2007 SP1)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  7. #7
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Combo Box with Filtered Numbers (2007 SP1)

    Attached.

    Thanks for taking a look.

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box with Filtered Numbers (2007 SP1)

    If I set the Row Source property of Combo20 (the combo box bound to the SRN field on the frmUndertakings form) to

    SELECT SRN FROM tblSRN WHERE SRN NOT IN (SELECT SRN FROM tblUndertakings)

    it correctly displays the SRN numbers that are not in use. However, this has the advantage that the list will not contain the currently selected SRN. To take this into account, you can change the Row Source to

    SELECT SRN FROM tblSRN WHERE SRN NOT IN (SELECT SRN FROM tblUndertakings) OR SRN=Forms!frmUndertakings!Combo20

    To ensure that the list is updated when the user moves to another record, use code in the On Current event of the form:

    Private Sub Form_Current()
    Me.Combo20.Requery
    End Sub

  9. #9
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Combo Box with Filtered Numbers (2007 SP1)

    Thanks Hans

    I used that Code, but Access prompts me for Combo20?
    I double-checked the spelling in case it was a typo and even changed the name to cboSRN, but it then just prompts me for a value in cboSRN?
    If I remove the OR statement, the dropdown is Empty?

    Also, I'm still not sure how how these numbers will be 'released' when the DateSent field becomes populated?

    Sorry to be a pain!

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box with Filtered Numbers (2007 SP1)

    Sorry, I hadn't taken into account that frmUndertakings is a subform. The Row Source should be

    SELECT SRN FROM tblSRN WHERE SRN NOT IN (SELECT SRN FROM tblUndertakings) OR SRN=Forms!frmPropertiesMain!tblSRBox!frmUndertakin gs!Combo20;

    but that causes a problem when the form is closed.

    I don't see a DateSent field anywhere in the database.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box with Filtered Numbers (2007 SP1)

    See the attached version. I have assumed that you meant DateGiven instead of DateSent.
    The Row Source of the combo box is updated when
    - The user edits the DateGiven text box.
    - The user moves to another record.
    Note: you'll have to relink the tables in the frontend to those in the backend.
    Attached Files Attached Files

  12. #12
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Combo Box with Filtered Numbers (2007 SP1)

    Thanks Hans.

    I think I can take it from here.

    I really apprecaite all your help, as always.

    Have a great day/week.

    I'll post back if I have any further questions, but I hope I won't have to bother you again!

    Take care, and thanks again.

    <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •