Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2005
    Posts
    13

    Unanswered: Checking form field data against subform table field(s)

    Hi. I have a little issue - I have a subform on a main form for item bookings. When an item is chosen in the cboItem field, the subform shows all the future bookings for that item - with From & To dates. I got the update for txtReturnDue field to show message "Already booked - choose another date" if the date chosen falls between the DateFrom and DateTo fields in the subform. However, it only works on the top/first record in this subform (form from a table) - if it falls between the two dates in the 2nd or more record then the msgbox/condition doesn't work. The code I have is :
    Private Sub txtReturnDue_AfterUpdate()
    If Me.txtReturnDue >= Me!qryBookingsSubform!DateFrom And Me.txtReturnDue <= Me!qryBookingsSubform!DateTo Then
    MsgBox "This Item is booked out for these dates. Check bookings table below and choose another date."
    Me![txtReturnDue].SetFocus
    End If
    End Sub

    How can I get it to work on ANY of the records in the table, rather than just the top one? Any suggestions greatly appreciated!

    Mark Norton

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Send a short example of your mdb. (access 2000 or 2002, zip).

  3. #3
    Join Date
    Jul 2005
    Posts
    13

    Thanks for checking

    Hi - The database is split into forms and Back end, so you will have to reset location when downloaded. Thanks again.
    Attached Files Attached Files

  4. #4
    Join Date
    Oct 2009
    Posts
    340
    didn't look at your db; but based on your description you need to work in queries for the solution;

    set up a query that determines a scheduling conflict or not and if you get a record - either put that value into the form as a subform - - or use a DCount just to see if a record exists in the query and thus signalling that there is a conflict....

  5. #5
    Join Date
    Jul 2005
    Posts
    13
    Hi - forgot to say which form has the txt field - it's 'frmLoanRecord_OUT'. I'm trying to figure out what you mean - i'll keep thinking about it...in essence what the form is is a loan form with a due date on it. A subform is on that form, which is the bookings table, and it populates the details of existing bookings for particular items, depending on what type of item is selected on the main form. I can get the warning message to come up for the first booking shown in that table - that works fine - but if a returnm or due date is selected that is the same as one of the existing bookings for a booking that is not the first record in the table, then it doesn't work. The code only works on the first record in the bookings table. I was wondering if loop or recordset functions hold anything? I'm a relative newbir=e to access or any DB work really, so am still learning about different functions.

    Thanks for looking at this BTW, very kind of you.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I took a quick look. Currently you have no criteria in the recordsource query of "qryBookingsSubform" other than DateFrom >=Date(). If the intent is to query the records of the subform based on what is selected in the frmBookings cboType and cboItem, you may want to put in additional criteria of the qryBookingsSubform recordsource and issue a me.qryBookingsSubform when the cbo_Type and/or the cboItem fields are updated.

    If you need to check for date ranges, you may want to use the dlookup or dcount in your code before or after the AfterUpdate events of cboType or cboItem.

    Without knowing specifically how your subform is supposed to work, it's difficult to say. Currently, your criteria of this subform is for DateFrom (ie. =>Date()). This may or may not be what you want.

    I would also recommend changing the background of unbound fields (such as to yellow or blue) to indicate these are "search" type fields. Otherwise it looks confusing on whether these are actual data fields being updated or search/filter type fields.

    Typically though, your subform has some kind of criteria in the recordsource so that when you issue a me.qryBookingsSubform.requery command, it then requeries this form to show only the records which match fit the values of your unbound fields on the main form.

    Ex:
    You can add criteria and under the Type or Item column, add criteria such as:
    Like Forms!frmBookings!cboType & *

    Then when you issue a me.qryBookingsSubform.requery command, it requries to show only records like the cboType (unbound field) on the main form.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Note: a me.refresh command does NOT necessarily refresh the recordset of the subform. You must put in code such as: me.qryBookingsSubform.requery to requery the recordset of the subform.
    Last edited by pkstormy; 02-12-10 at 12:53.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Jul 2005
    Posts
    13
    That's great - many thanks for the analysis - some great advice there.

    You said: "Without knowing specifically how your subform is supposed to work,..." - actually the subform is simply for information only. In the absence of me having the ability(!) to make it so that you are UNABLE to double book - (ie so you are prevented from loaning an item out on the day someone else has booked it out for)- I simply tied the Item fields together, so that when an Item is chosen from the main form, all the future bookings for that item are shown, for reference. So you are supposed to check the subform before completing the loan. Items have been loaned out before the person who has booked it for THAT day has had the chance to come and pick it up.

    In the last few days I was attempting to make it so you are UNABLE to loan an item if the dates you are loaning it out for are already accounted for in the booking table subform. It's been tying me in knots a bit!

    ...and if the 'after update' event for txtReturnDue (label: Return Due) would just go through all the records in the bookings table subform that would have sufficed, but I just cannot make it reference anything but the first record in the subform. Thanks for your advice to date, however, this is going to be very useful indeed.

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You could also design a duplicates query (using the wizard) on the Type, Item and DateFrom and then use the dcount to see if there is a duplicate or show that duplicates query in your subform (again, using criteria to return only those records matching the unbound values).

    Ideally, you could also make any fields you don't want duplicated in the table as a combined primary key in the table (ie. Type, Item and DateFrom will have the key symbol next to them in table design). That would ensure that the same Type, Item and DateFrom could not be duplicated in the table.

    Otherwise, If I want to see if there's matching value(s) in a table, I might write a function saved in a module as such:

    Function HasDupRec(vType as variant, vItem as variant, vDateFrom as date) as boolean
    Dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from myTableName where Type = '" & vType & "' and Item = '" & vItem & "' and DateFrom = #" & vDateFrom & "#"
    rs.open strsql,currentproject.connection, adopenkeyset, adlockreadonly
    if rs.eof and rs.bof then
    msgbox "No matching record"
    HasDupRec = false
    else
    msgbox "There are: " & rs.recordcount & " matching records."
    HasDupRec = true
    end if
    rs.close
    set rs = nothing
    End function

    Then you'd call the function once all your unbound fields are populated (or make a button which 1st checks that they all have values and then calls the function)

    ex:
    if isnull(me!cboType) then
    msgbox "You must first enter the type"
    exit sub
    end if
    if isnull(me!cboItem) then
    msgbox "You must first enter the item"
    exit sub
    end if
    if isnull(me!DateFrom) then
    msgbox "You must first enter the DateFrom"
    exit sub
    end if
    if HasDupRec(cboType, cboItem, DateFrom) = true then
    msgbox "Another record exists.
    Last edited by pkstormy; 02-13-10 at 22:36.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    example

    Something like this also might work... - just look at the qryBookingsSubform criteria and the AfterUpdate events of cboType, cboItem and DateFrom.

    But you'd want to tweak it to meet your needs.
    Attached Files Attached Files
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Jul 2005
    Posts
    13

    Thanks a million

    PKStormy, you are extremely generous with your time! Many Thanks! You've given me LOADS of tips and hints here, and over the next few days as I digest and experiment what you have said, I'm sure I'll resolve this one way or another using one of the alternatives you suggested. I'll let you know how I get on. Have a great week!

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You can find additional tips here:

    http://www.dbforums.com/microsoft-ac...de-bank-6.html

    or anywhere throughout the code bank. Keep in mind that there's more than 1 way to do what you want to do. I showed you how I typically do it but others may have their own ways.
    Last edited by pkstormy; 02-15-10 at 23:28.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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