Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VB Coding problem......again (2k)

    Hi,
    I've attached a database that I've been working on (largely relying on the exemplary advice of Hans). I've got a few things to do to complete it, but I've become stuck at the vb coding behind a control.
    What I'm trying to get is before a record is deleted from the main form the user has to answer certain questions.
    In order:
    1)Do you want to remove this file completely? (vbYesNo) if yes then step 2......if no then exit sub
    2)Do any of the space details of the box/es need to be updated? (vbYesNo) if yes then step 3......if no then delete file
    3)Please enter details for each box (vbOK) then Set Focus to the Subform, Notes field, so that the user can enter the details of the space that is being made.
    The vb message in point 3 is mainly to tell the user that details have to be entered for each boxnumber. I'm not sure whether there is a way to SetFocus for each box dependant upon whether Yes has been entered in point 2, hence this approach.
    Any help would be vastly appreciated.
    Attached Files Attached Files

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

    Re: VB Coding problem......again (2k)

    If the user still has to enter details, the only thing you can do is set focus to the first Notes box, and get out. The user will have to click the Delete button again when (s)he has finished entering the details. You cannot continue the delete automatically. So try this:

    Private Sub cmdDelete_Click()
    Dim strSQL As String
    If MsgBox("Do you want to remove this file completely?", _
    vbYesNo + vbQuestion + vbDefaultButton2) = vbNo Then Exit Sub
    If MsgBox("Do any of the space details of the box/es need to be updated?", _
    vbYesNo + vbQuestion + vbDefaultButton2) = vbYes Then
    ' Set focus to Notes
    Me!ArchiveSub.SetFocus
    Me!ArchiveSub!Notes.SetFocus
    Exit Sub
    End If
    DoCmd.SetWarnings False
    ' Set BoxFull to No
    strSQL = "UPDATE qryURNBoxes SET BoxFull = False WHERE URN = " & _
    Chr(34) & Me.URN & Chr(34)
    DoCmd.RunSQL strSQL
    ' Delete the file
    RunCommand acCmdDeleteRecord
    DoCmd.SetWarnings True
    End Sub

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Coding problem......again (2k)

    Thats great Hans, works a treat.
    Sorry another question, with regards to conditional formatting, is it possible to change the colour of the text in the drop list as well as the actual combobox?
    Currently I've done it so that the user can clearly see from the colour of the text in the combobox (BoxNumber) that the box is marked as full. But the actual drop list doesn't display the colours, therefore eventually it will be a bit of a stab in the dark to get an empty box as this will not be displayed in the list, only when its displayed in the Form.

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

    Re: VB Coding problem......again (2k)

    No, conditional formatting only applies to the text box part of the combo box, not to the list part.

    But you can display the BoxFull field in the dropdown list, by making sure it is included in the row source, increasing the column count by 1 and specifiying a non-zero column width for the new column.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Coding problem......again (2k)

    Finally, and I mean finally!!!!!!!!!!!!! Hoorah I hear you cry.........
    None of my comboboxes on any of the forms seem to be displaying the correct data on the form where an entry is selected and can I work out why........................(its been a long day)
    And some of the sub forms are not displaying as I thought they would..................
    And thats it......at last.
    Attached Files Attached Files

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

    Re: VB Coding problem......again (2k)

    Yes, you seem to have messed up things royally <img src=/S/sad.gif border=0 alt=sad width=15 height=15> Have you gone back to an earlier version of the database? Many of the things that are wrong had been fixed over the last few weeks.

    To name but a few:

    TblArchive should NOT contain a field Box Number.

    You have set the Data Entry property for FrmDatabaseMain to Yes. This means that you can only enter new records, not view existing ones. I don't think that's your intention, so you should set Data Entry to No.

    You have set Allow Edits for FrmDatabaseMain to No, so you can't select anything from combo boxes. You should set Allow Edits to Yes.

    You have set the Record Source of FrmDatabaseSub to TblBoxNumbers. It should be a query based on TblURNBox and TblBoxNumbers. We have been through this before, so please look back at your earlier thread. I posted a working example for you somewhere in there.

    FrmDatabaseSub should NOT be linked to FrmDatabaseMain by Box Number, but by URN.

    Etc. etc.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Coding problem......again (2k)

    I see what I have done now. I had tried to copy previous forms to create duplicates to save time.
    I have restarted from the original I have saved and expanded it without rushing it. All seems to be working now, at last!!!!!!!!!!!!!
    One question though. I have a combobox that I would like to keep a true/false value in, but display something other than Yes/No or True/False text for the user. Is this possible?

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

    Re: VB Coding problem......again (2k)

    Yes, that is possible, by adding an extra column with the display values and hiding the first column with the actual True/False value. You must set Column Count to 2 and Column Widths to 0";1".

    You can set Row Source Type to Value List, and Row Source to something like
    -1;"Yes, please";0;"No, thank you"
    or you can create a custom table

    <table border=1><td>Flag</td><td>DescriptiveText</td><td align=right>-1</td><td>Yes, please</td><td align=right>0</td><td>No, thank you</td></table>
    and set Row Source Type to Table/Query, and Row Source to the name of this table.

Posting Permissions

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