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.
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.
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.
So the receiptID will be number data type, am i 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.
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
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:
Dim dbs As DAO.Database
Set dbs = CurrentDb
Dim rstTable As DAO.Recordset
Set rstTable = dbs.OpenRecordset("TblName", dbOpenDynaset)
!FldName1 = me.txtboxName1
!FldName2 = me.txtboxName2
'repeat as necessary
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
Set the Enabled property of your Save button to No.
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
ctl.BackColor = vbWhite
If blnComplete Then
Me.CmdBtn_ReqNew_Finish.Enabled = True
Now the user can't save the record until all the necessary info is filled in. Try it out.
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?
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?
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.