Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Mar 2004
    Posts
    660

    Unanswered: close data entry form

    Hello, in my data entry form, I have most of fields are required. When i try some situaton such as leave some fields with no value. And then i try to close the form use close icon. It said it can't save the record. Do you want to close the database object anyway, if i click yes, I check the primary key named receiptID. The next record receiptID won't be continue. For example, the previous receiptID is 56, if i use close icon with some fields no value and i close the database, the next receiptID will be 58. It lost receipID 57. We don't allow lost receiptID. as we will always check the sequence of receiptID. The way i am thinking is give all the required field with "*". Do you have any good idea. The most important thing is the receiptID that is automatically and must be in sequence.

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    You'll need to either use an unbound form or generate the receiptID rather than using an autonumer field.

  3. #3
    Join Date
    May 2005
    Posts
    1,191
    I don't like to use Access's default way of adding new records (the "new" button on the navigation buttons on forms). It gives all sorts of troubles like your describing.

    Instead, I like to create an unbound form with text boxes and combo boxes and everything I need on the form. This allows for great data input control as well. Then when the user is done, have a save button that loads all the data into your table. At this time you can do a dmax() to find the last number, add one, and use that as your ID.

    HTH
    Me.Geek = True

  4. #4
    Join Date
    Mar 2004
    Posts
    660
    Oh, i am going to do this way. So the receiptID will be number data type, am i right? And the next receiptID use function dmax(), I never use this function. Is that i need to use dmax(receiptID)? I think this is wrong. If possible, could you please give an sample code. Many thanks.
    Last edited by yyu; 08-10-07 at 11:28.

  5. #5
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by yyu
    So the receiptID will be number data type, am i right?
    Right.

    Check out the help file on the dmax() function, it'll tell you everything you need to know about it. Just remember to add 1 to it to get the next number. I have used this method in the past with previous projects, and I've found many benefits to it. Give a try at it, test your code out a little, and post back if you have questions then.
    Me.Geek = True

  6. #6
    Join Date
    Mar 2004
    Posts
    660
    I am going to try that function. I have a question, you use unbound in the form so that when you click the save button. I use the following code. Is that correct? Could you please check for me. Also i would like to have the Receipt date to always show the current date. If i use bound to table, in the date i will give the default value as date(). If unbound, how to do it? Many thanks.

    Private Sub Save_Click()
    On Error GoTo Err_cmdSave_Click

    ReceiptDate = txtReceiptDate
    Fund = txtFund
    Category = txtCategory
    ReceivedFName = txtReceivedFName
    ReceivedLName = txtReceivedLName
    .....

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    Exit_cmdSave_Click:
    Exit Sub

    Err_cmdSave_Click:
    MsgBox Err.Description
    Resume Exit_cmdSave_Click
    End Sub

  7. #7
    Join Date
    May 2005
    Posts
    1,191
    Actually, on the On Click, you're going to have to either build an APPEND query in VBA and execute that from within the code, or (the method I prefer for this type of thing) use the recordset.addnew method. Either way works, depends what you're more comfortable with I think. Do a help on this method (put cursor over the word "addnew" and press F1), and it should give you some good help. To get you started, do something like:
    Code:
    Dim dbs As DAO.Database
    Set dbs = CurrentDb
    Dim rstTable As DAO.Recordset
    Set rstTable = dbs.OpenRecordset("TblName", dbOpenDynaset)
    With rstTable
        .AddNew
        !FldName1 = me.txtboxName1
        !FldName2 = me.txtboxName2
        'repeat as necessary
        .Update
    End With
    See where you can go with that and post back.
    Me.Geek = True

  8. #8
    Join Date
    May 2005
    Posts
    1,191
    Another thing I like to do (that's advantageous of this method) is to make sure the user fills in the data they need to. To do this, use the Tag property; for each field that needs to be filled in put "Required" in the tag property of the control. Then on certain events for these controls, do something like
    Code:
    Call RequiredFields
    Set the Enabled property of your Save button to No.
    Code:
    Private Sub RequiredFields()
    
    Dim eYELLOW As Long
    eYELLOW = 10092543
    
    Me.CmdBtnReqNewFinish.Enabled = False
    Dim ctl As Control, blnComplete As Boolean
    blnComplete = True
    For Each ctl In Form.Controls
        If ctl.Tag = "Required" Then
            If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
                If Nz(ctl, "") = "" Then
                    ctl.BackColor = eYELLOW
                    blnComplete = False
                Else
                    ctl.BackColor = vbWhite
                End If
            End If
        End If
    Next
    
    If blnComplete Then
        Me.CmdBtn_ReqNew_Finish.Enabled = True
    End If
    
    End Sub
    Now the user can't save the record until all the necessary info is filled in. Try it out.
    Last edited by nckdryr; 08-10-07 at 13:03.
    Me.Geek = True

  9. #9
    Join Date
    Mar 2004
    Posts
    660
    That is really lot of work. I am going to learn and back to you. I have a question, my old PC when i click the button in the tool box, it didn't show the command button winzid. Could you please tell how to set it up?

  10. #10
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by yyu
    I have a question, my old PC when i click the button in the tool box, it didn't show the command button winzid. Could you please tell how to set it up?
    If I understand your question correctly, there should be a magic wand in the tool bar, click that to get the wizard. I think that's what you were asking. I don't use the wizard much.
    Me.Geek = True

  11. #11
    Join Date
    Mar 2004
    Posts
    660
    Sorry, i didn't find it. So you always write your own code. Do we need the code in the save button:

    On Error GoTo Err_cmdSave_Click

    Exit_cmdSave_Click:
    Exit Sub

    Err_cmdSave_Click:
    MsgBox Err.Description
    Resume Exit_cmdSave_Click


    Thank you very much!

  12. #12
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by yyu
    Sorry, i didn't find it. So you always write your own code.
    I either write code from scratch, modify code that I've written for previous projects, or use others' codes, with permission of course (standing on the shoulders of giants and all that).
    Quote Originally Posted by yyu
    Do we need the code in the save button:

    On Error GoTo Err_cmdSave_Click

    Exit_cmdSave_Click:
    Exit Sub

    Err_cmdSave_Click:
    MsgBox Err.Description
    Resume Exit_cmdSave_Click
    There's not really any code here, it's just error handling. What are you trying to do with this code?
    Me.Geek = True

  13. #13
    Join Date
    Mar 2004
    Posts
    660
    I am VBA beginer. Please allow me to ask you all many questions. In the table, what i need to do is just create field name and data type. I don't need an to set required field. As this is handle in form. Am I right? Suppose, the user type the data with wrong data type. Did you need to write error hander?
    Last edited by yyu; 08-10-07 at 14:12.

  14. #14
    Join Date
    May 2005
    Posts
    1,191
    Well, if you want something easy, do something similar to what you were doing with the default Access way. Then on the On Open event of the form, try to use the dmax() + 1 way of doing things, to "automate" the number field. You'll have to study this method a little bit to understand it, but there's no "wizard" way of doing it; you'll just have to learn. Just try some of your own code out, and if you get stuck, you can always come back to ask questions.
    Me.Geek = True

  15. #15
    Join Date
    Mar 2004
    Posts
    660
    I am doing now. I have question about the date. When i save it. It said data type conversion error.

    My code is:
    !Amount =Me.txtAmount

Posting Permissions

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