Results 1 to 9 of 9

Thread: Extra Record

  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have a form and subform, from which I run a report. Some of the details in the subform are pulled in from another source. As you can see from the attached image. It says there are 5 records, but one of these is basically empty, apart from the 0-0 in the Allocated X-ray numbers. This is a formula which works out the X-ray numbers. How can I eliminate blank records? Another associated problem is total blank forms. Because the Serial Number is autonumber, no serial number is created until another field is filled in. If a user then enters an order number on the main form, but then doesn't put any details in the subform for any reason, a basically blank record is created. I have discovered there are loads of these in the database. Is there a way of stopping a record being created, unless details are entered into the subform?[attachment=83273:Extra_record.jpg]
    Attached Images Attached Images

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Notice the little "pencil" icon at the left, this indicates the record is dirty. Unless you physically did something to cause this, it means you have code that is in some way dirtying the record merely by selecting it. Perhaps it is setting the value of that "Allocated" field to "0-0"?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Apologies for not replying before Mark, but I've only just got round to this problem again.Your right, the "Allocated" field, is filled in automatically, when the Batch No is filled in. It looks as though the user is pressing enter, to make sure the data is entered, then not completing the last line. How can I delete/unsave records that have no Batch No. This is the only thing that is manually entered. The database is full on 'empty' records created like this, that I manually go in and delete, to keep it tidy.
    Also, because this is a Subform, if the user completes part of the Main form (Date, Order No, etc), then (because of the tab order) enters the details on the Subform, as soon as he returns to the Main form , data in the Subform is lost. How can I prevent this?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Create a Before Update event procedure for the subform, and set

    Cancel = True

    if you don't want the record to be saved, for example if Batch No is null. Use MsgBox to tell the user what's wrong.

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    [quote name='robm' post='797919' date='14-Oct-2009 11:11']Apologies for not replying before Mark, but I've only just got round to this problem again.Your right, the "Allocated" field, is filled in automatically, when the Batch No is filled in. It looks as though the user is pressing enter, to make sure the data is entered, then not completing the last line. How can I delete/unsave records that have no Batch No. This is the only thing that is manually entered. The database is full on 'empty' records created like this, that I manually go in and delete, to keep it tidy.
    Also, because this is a Subform, if the user completes part of the Main form (Date, Order No, etc), then (because of the tab order) enters the details on the Subform, as soon as he returns to the Main form , data in the Subform is lost. How can I prevent this?[/quote]
    There is no reason the data in the subform should be lost. When focus moves away from the subform, Access will automatically save the data. The only thing I can think of is that the user is moving to another record on the mainform, so the subform redisplays.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks to you both. I put code on the OnCurrent event, as there was code in the create numbers if the Batch No was completed. So if they don't put a Batch NO in, it doesn't create a record. With regards to the losing focus, I discovered this was because they would open the form at a new record, which has an Autonumber field. Then they would enter data in the subform, without entering data on the mainform first, so no autonumber was created. When the form was filled in, as you said Mark, the subform re-displays. I have now put code in the form, so that unless the Order No is filled in, they can't go any further. So thanks for pointing me in the right direction. On occasion, they have entered data on the mainform, but not entered any details on the subform. This creates blank records. Although I have asked that they use the last one created, they still appear. I don't suppose there is a way round this is there.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If the user enters data in the main form but not in the subform, no record should be created in the subform unless you use code to set a value in the subform.

  8. #8
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have this code which prevents from duplicating records, and deletes the record. It works the first time round, but if they enter the wrong Batch No again a second time it allows them. Have I missed something out? How do I delete the record if batch No is duplicated?

    Code:
    Private Sub ALLOCATED_X_RAY_No_s_BeforeUpdate(Cancel As Integer)
    
    Dim cnn As ADODB.Connection
    	Dim rst As ADODB.Recordset
    	Dim strSQL As String, strCode As String, strSuffix As String, strStart As String, strEnd As String
    	Dim lngStart As Long, lngEnd As Long
    	Dim lngSplit As Long
    	Set cnn = CurrentProject.Connection
    	strCode = Left(ALLOCATED_X_RAY_No_S, 2)
    	lngSplit = InStr(ALLOCATED_X_RAY_No_S, "-")
    	strStart = Trim(Mid$(ALLOCATED_X_RAY_No_S, 4, lngSplit - 4))
    	strEnd = Trim(Mid$(ALLOCATED_X_RAY_No_S, lngSplit + 1))
    	If CStr(Val(strStart)) = strStart Then
    		strSuffix = ""
    	Else
    		strSuffix = Right$(strStart, 1)
    	End If
    	lngStart = Val(strStart)
    	lngEnd = Val(strEnd)
    	strSQL = "SELECT COUNT(*) FROM [RELEASE NOTE X-RAY DETAILS]WHERE [Part No] = '" & Me![Part No] & "'  AND Code = '" & strCode & "' AND [Suffix] = '" & strSuffix & "'"
    	strSQL = strSQL & " AND (([Start] <= " & lngStart & " AND [End] >= " & lngStart & ") OR ([Start] <= " & lngEnd & " AND [End] >= " & lngEnd & ") OR ([Start] >= " & lngStart & " AND [End] <= " & lngEnd & "))"
    	Set rst = New ADODB.Recordset
    	rst.Open strSQL, cnn, adOpenForwardOnly
    	If rst(0) >= 1 Then
    		MsgBox "This serial number range overlaps an existing one. Please double-check and try again."
    		DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    		DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70 'Cancel = True
    	   'Cancel = True
    	Else
    		Me.Code = strCode
    		Me.Start = lngStart
    		Me.End = lngEnd
    		Me.Suffix = strSuffix
    	End If
    	rst.Close
    	Set rst = Nothing
    	Set cnn = Nothing
    End Sub

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Instead of deleting the record, set Cancel = True and let the user correct the input.

Posting Permissions

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