|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| ||||||||||||||||||||||||||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
October 11th, 2009, 09:38 PM
|
|||
|
|||
|
AutoNumber to generate only if field has value
I'm not really sure if this is possible but would appreciate any feedback or thoughts.
I have a field fld_FeedbackNumber which is set as an Autonumber. The problem is not the autonumber itself, but when a user opens the database to input a new record and doesn't enter any data then exits. This effectively creates a blank record. To try and combat this i created a subform that opens first and has 4 buttons, Log New, Update Existing, Reports & Cancel/Exit. For some reason people still get it wrong and press Log New when they do not want to Log New which generates a new blank record after the last. I have a couple of other ideas on how to avoid this but am not sure how to execute them. A: Set the autonumber field to not generate the number until the last required field has been updated & exited, or the submit/exit button has been pressed (which is another button i have on my main form). B: OnOpen - If user presses Log New and fld_ClaimNumber (the set focus field when Log New is pressed) = "" then goto that record instead of creating a new record. This is probably not the best idea though because if there is more than 1 blank record it would presumably go to the last blank record. C: Have a dialogue box that comes up and says "If you have pressed Log New and do not intend to log a new feedback then you have just created a blank record which will forever remain blank unless the administrator goes and rectifies your mistake, thus wasting their valuable time - thank you". If there is a better suggestion out there i would be glad to hear it as it's quite frustrating having to go and remove blank records then cut paste the table into another database, then cut and paste back into original database just to keep the autonumber incremental without gaps.
|
|
#2
October 11th, 2009, 10:51 PM
|
|||
|
|||
|
Item A: You cannot set the AutoNumber field to not generate. My experience is that once you enter a field on that row, the number is committed.
Item B: You could have code that finds the first blank record, would have to use a field that would always have data for a completed record. Item C: Better to find a solution that handles the idiots, never perfect, but just have to handle each situation as they arise. Another option is to not care what the autonumber value is and utilize a structured unique identifier, which is actually better database design. My project uses code to create a unique ID for each record (these are laboratory sample ID's, structured like yyyyA-9999). I do have one table with an AutoNumber field that is used to identify a unique project and is a foreign key for the sample table and is used nowhere else. It is a pain to deal with but I tolerate it because otherwise I would have a compound unique key composed of 4 fields. This one table is the only table that even has an autonumber field. Pretty much everything else is linked by the sample ID. Last edited by June7 : October 11th, 2009 at 10:54 PM.
|
|
#3
October 11th, 2009, 11:15 PM
|
|||
|
|||
|
Hi June7, thanks for the response.
Looks like Option B is the answer. As for the idiots, i found that no matter how idiot proof you make something they create a better idot. Can you give me some assistance on how i would go about the code for finding the first blank record and setting it look for it etc. The If criteria would have to be if fld_ClaimNumber = "" Then Search or find etc etc. I had never used access before i started this database so it's been one massive learning experience.
|
|
#4
October 12th, 2009, 02:09 PM
|
|||
|
|||
|
Code from my project. Looks for record with sample ID but no other data because of aborted login. Once sample ID is established, opens data entry form to existing or new record.
Code:
Private Sub btnLoginSample_Click()
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim strLabNum As String
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
'search for aborted lab number and use it, else if none then GetNewLabNumber
rs.Open "SELECT LabNum FROM Submit WHERE IsNull(DateEnter);", cn, adOpenStatic, adLockPessimistic
If rs.RecordCount > 0 Then
rs.MoveFirst
'strLabNum is retrieved number from an aborted login
cn.Execute "UPDATE Submit SET DateEnter=#" & Date & "#, EnterWho='" & gstrUser & "' WHERE LabNum='" & rs!LABNUM & "'"
strLabNum = rs!LABNUM
Else
strLabNum = GetNewLabNumber
cn.Execute "INSERT INTO Submit (LabNum, DateEnter, EnterWho) VALUES ('" & strLabNum & "', #" & Date & "#, '" & gstrUser & "')"
End If
rs.Close
Me.tbxLABNUM.SetFocus
DoCmd.OpenForm "Sample", acNormal, , "LabNum='" & strLabNum & "'", , , strLabNum & "," & "Login"
End Sub
Code:
Public Function GetNewLabNumber() As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strLabNum As String
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open "SELECT LabNum FROM Submit ORDER BY LabNum DESC", cn, adOpenForwardOnly, adLockPessimistic
If rs.EOF = False Then
strLabNum = rs.Fields(0)
If CDate(Left(strLabNum, 4)) = DatePart("yyyy", Date) Then
strLabNum = Left(strLabNum, 6) & Format(Right(strLabNum, 4) + 1, "0000")
Else
strLabNum = DatePart("yyyy", Date) & "A-0001"
End If
Else
strLabNum = DatePart("yyyy", Date) & "A-0001"
End If
rs.Close
GetNewLabNumber = strLabNum
End Function
Code:
strID = Nz(DLookup("IDfieldname", "tableName", "Isnull(criteriafieldname)"), 0)
If strID = 0 Then
'code to open form to new record
Else
'code to open form to existing record
End If
'I use Isnull because I don't allow empty strings in my tables.
'If you do then modify to something like:
strID = DLookup("IDfieldname","tableName", "criteriafieldname & ''=''")
If strID = "" Then
...
'Am not sure about the empty string handling as I have never needed this in a DLookup Where clause
Last edited by June7 : October 12th, 2009 at 02:36 PM.
|
|
| Viewing: ASP Free Forums > Programming > Visual Basic Programming > AutoNumber to generate only if field has value |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
