How to use a Form to Make entries on a Table

[egnsantiago]

Hi all,

I'm new to the forum and to Access. I need help on how to use a Form to Make entries on a Table. I was able to create the form from the Table. On the form, I only have the fields I need to make a new entry on the Table and a button with VBA code to generate a sequential ID number (SIN) on the second column. So a few thing are not working fine.

When I click on the button, the VBA code generates the SIN but I'm not able to see it on the form. I thought this was just because the cell was on edit mode. However, if I move to the another fields on the form a plug in some data, I can actually see the data I type in, but the SIN is still not visible. After typing in the data and closing the form, I open the Table and I see that the SIN is there in a row but the data that I typed in for that record is in the next row. That defeats the purpose of the form which is create a new record in a single row with a custom SIN.



Can someone point me into the right direction?

Below is the VBA code I'm using to generate the sequential ID number (CaseId).

Private Sub Command13_Click()

Dim strCaseNum As String

'search for aborted case number and use that record, else if none then create new record
strCaseNum = Nz(DLookup("CaseId", "ObsoCases", "IsNull(Created)"), "")
If strCaseNum <> "" Then
    CurrentDb.Execute "UPDATE ObsoCases SET Created=#" & Date & "# WHERE CaseId='" & strCaseNum & "'"
Else
    strCaseNum = Nz(DMax("CaseId", "ObsoCases"), "")
    If strCaseNum = "" Then
        'this accommodates very first generated number of blank database
        strCaseNum = Format(Date, "yy") & "-0001"
    Else
        'this accommodates change in year
        If Left(strCaseNum, 2) = CStr(Format(Date, "yy")) Then
            strCaseNum = Left(strCaseNum, 3) & Format(Right(strCaseNum, 4) + 1, "0000")
        Else
            strCaseNum = Format(Date, "yy") & "-0001"
        End If
    End If
    CurrentDb.Execute "INSERT INTO ObsoCases(CaseId, Created) VALUES('" & strCaseNum & "', #" & Date & "#)"
End If


End Sub

Thanks,
The Newbie

[John_G]

What you want to do is generate a SIN for the current record, i.e. the one you are editing - is that correct?

If so, then this statement:

CurrentDb.Execute "INSERT INTO ObsoCases(CaseId, Created) VALUES('" & strCaseNum & "', #" & Date & "#)"

does not do that - it is creating an entirely new record in the table ObsoCases.

Assuming that your form has a control (field) for the SIN (is that the same as CaseID?), all you need to do is use:

Me!CaseID = strCaseNum instead of the Insert.

The same applies to the other option as well - use Me!CaseID = strCaseNum instead of the Update.

[June7]

Gee, that is based on example code I posted.

Unless I posted an older vision that was a Sub, procedure was a Function called NewSample that returned the sample number to calling procedure. I made it a function in general module because it was called from 3 locations.

The process:

1. click button on menu form to log in a new laboratory sample.

2. function finds existing sample number record from previously aborted login or generates a new sample number and commits record to table

3. open form filtered to that sample number record to enter remainder of info

Code in button click calling the function and opening form:

         strLabNum = NewSample
         DoCmd.OpenForm "Sample", acNormal, , "LabNum='" & strLabNum

One reason for this process is to virtually eliminate chance of multiple users generating same sample number. The sample number is generated and committed to table immediately. Alternative is to generate sample number at end of data input when record is committed to table. However, it was difficult to do this and still allow user to view sample number before continuing with entering other samples. Users needed to see number to write it down on submittal document.

If you are already in a new record, then certainly don't want code that looks for an aborted record nor inserts a record. You just need the part that generates the number. Then as John said, populate field of that record. And immediately save record to table so other users won't generate the same number. I also considered this approach but decided it just didn't suit, so I eventually settled on the process outlined above.

[egnsantiago]

That's correct June7. I found your code looking for a solution. I know how to code on Excel but this is my first time doing something like this on Access. Your code has been useful so the credit is all yours .

I'll try to incorporate your recommendations and Jhon's as well and will let you both know if I was successful changing the code or not. Wish me luck!

Thanks,
The Newbie

[egnsantiago]

OK, so after a few tries I was able to make it work. The is how the code looks now:

Private Sub Command13_Click()

Dim strCaseNum As String

strCaseNum = Nz(DMax("CaseId", "ObsoCases"), "")

    If CaseId = "" Then
        strCaseNum = Format(Date, "yy") & "-0001"
    
    Else
        If Left(strCaseNum, 2) = CStr(Format(Date, "yy")) Then
            strCaseNum = Left(strCaseNum, 3) & Format(Right(strCaseNum, 4) + 1, "0000")
        Else
            strCaseNum = Format(Date, "yy") & "-0001"
    End If
    
    Me.CaseId = strCaseNum

    End If
End Sub

Private Sub Command19_Click()
DoCmd.RunCommand acCmdSaveRecord
MsgBox "New Record Saved!"
Me.Requery
End Sub

Private Sub Form_Load()
DoCmd.GoToRecord acDataForm, "ObsoCases", acNewRec
End Sub

Thanks for your guidance!

Newbie

PD: Let me know if I still have some code that is unnecessary.

[June7]

Really should commit record to table in Command13_Click event. If you wait until user clicks Command19, another user may have generated the same CaseNum value.

Also, don't want to allow user to overwrite an existing CaseNum. Suggest only make the button available when focus is on a new record. Code in form Current event:

Me.Command13.Enabled = Me.NewRecord

Really should give buttons better names.

[egnsantiago]

Really should commit record to table in Command13_Click event. If you wait until user clicks Command19, another user may have generated the same CaseNum value.

Also, don't want to allow user to overwrite an existing CaseNum. Suggest only make the button available when focus is on a new record. Code in form Current event:

Me.Command13.Enabled = Me.NewRecord

Really should give buttons better names.

I see, but how do I commit the record to table in Command13_Click event?

Can I simply add DoCmd.RunCommand acCmdSaveRecord after Me.CaseID=strCaseNum?
Or should I use Me.Dirty instead?

If Me.Dirty Then
    Me.Dirty = False
End If

[June7]

Either one should work.

[egnsantiago]

OK. The form is now working better than what I initially thought. Thanks for your inputs.