Solved Closing a continuous form after double clicking on the last record (1 Viewer)

[Sam Summers]

I have a continuous form which displays various records of varying amounts.

The user double clicks on each record to carry out a task.

On double clicking on the last record the form closes with this code:

Private Sub Form_DblClick(Cancel As Integer)
On Error GoTo Err_Form_DblClick
    
    DoCmd.OpenForm "SelectAffected", acNormal, , "[HazardRef] = " & Me.HazardRef, acFormEdit
    
    If Me.Form.CurrentRecord = Me.Form.RecordsetClone.RecordCount Then
        DoCmd.Close acForm, "HazardList", acSaveYes
    Else
        DoCmd.GoToRecord , , acNext
    End If

Exit_Form_DblClick:
    Exit Sub

Err_Form_DblClick:
    MsgBox Err.Description
    Resume Exit_Form_DblClick
End Sub

The only problem is that on double clicking any records between the first and last record brings up a blank form?

I'm obviously missing something as the code does the job apart from that.

Many thanks in advance

 

[arnelgp]

is the field [HazardRef] a Number?
if string, you need to delimit it:

DoCmd.OpenForm "SelectAffected", acNormal, , "[HazardRef] = '" & Me.HazardRef & "'", acFormEdit

 

[Sam Summers]

is the field [HazardRef] a Number?
if string, you need to delimit it:

DoCmd.OpenForm "SelectAffected", acNormal, , "[HazardRef] = '" & Me.HazardRef & "'", acFormEdit

Hi Arnel, HazardRef is a number

 

[arnelgp]

ok, do you have it on your Form?

 

[Sam Summers]

ok, do you have it on your Form?

The Me.Hazard Ref is a combo box with two columns and the HazardRef on the "SelectAffected" form is a number

 

[arnelgp]

what is the Bound field for this combo?
try using it on openform WhereCondition.

 

[Sam Summers]

what is the Bound field for this combo?
try using it on openform WhereCondition.

I might have to get back to you with this one as i am having all kinds of problems........

 

[Sam Summers]

I might have to get back to you with this one as i am having all kinds of problems........

So now i have this code and i have been on this all day and i am still nowhere!!!!! I havent a clue what i'm doing!!!

Private Sub Form_DblClick(Cancel As Integer)
On Error GoTo Err_Form_DblClick
    
    DoCmd.OpenForm "SelectAffected", acNormal, , "[HazardID] = " & Me.HazardID
    
If Not Me.NewRecord Then
    DoCmd.OpenForm "SelectAffected", _
        WhereCondition:="HazardID=" & Me.HazardID
End If
    
    If Me.Form.CurrentRecord = Me.Form.RecordsetClone.RecordCount Then
        DoCmd.Close acForm, "HazardList", acSaveYes
    Else
        DoCmd.GoToRecord , , acNext
    End If

Exit_Form_DblClick:
    Exit Sub

Err_Form_DblClick:
    MsgBox Err.Description
    Resume Exit_Form_DblClick
End Sub

I'm off for a bottle of whisky!!!

 

[Gasman]

Walk through the code line by line and inspect the variables?

 

[Sam Summers]

Walk through the code line by line and inspect the variables?

Baffled with this one but i have established that the code or something is not seeing past the first record for some reason and i get the message "You cant go to the specified record"

 

[Gasman]

Are your sure?
I would expect DoCmd.GoToRecord , , acNext not to work and produce that message if you are on the last record.?

This is why I suggested walking through the code so you can SEE what lines are acted upon?

 

[Sam Summers]

Are your sure?
I would expect DoCmd.GoToRecord , , acNext not to work and produce that message if you are on the last record.?

This is why I suggested walking through the code so you can SEE what lines are acted upon?

Thats what i thought and that is why i did walk through it and at each "Me.HazardID" it equals 1 so i am guessing that to be the first record?

 

[Sam Summers]

Thats what i thought and that is why i did walk through it and at each "Me.HazardID" it equals 1 so i am guessing that to be the first record?

Just found this which must be the reason:-

A Label control on a form cannot receive the Focus, so when you click on the label in another record the current record does not change and you keep getting the same RecordID. Note that if you put the same code into the Click handler for a textbox (or some other control that can receive the Focus) then the current record will change and you'll get the RecordID of that record.

Although as i am using the double click Event on the record selector it may not be?

 

[Gasman]

I just tried a double click on a record selector and it gives me the correct ID ?

 

[Sam Summers]

I just tried a double click on a record selector and it gives me the correct ID ?

Oh well, there is something wrong somewhere?

 

[Gasman]

Oh well, there is something wrong somewhere?

Can you upload a sanitised, reduced DB with instructions on how to replicate?

 

[Sam Summers]

Can you upload a sanitised, reduced DB with instructions on how to replicate?

I have changed it slightly recently. This is the full version but i recently logicalised it better than it was. You have to use the Shift key to bypass. From the main form it is fine (i think) until step three and after where i seem to have problems which is ultimately affecting the report (Main body and Risk Assessment).
So from double clicking the subform records on the form 'HazardList' i just get a blank form?

Hope this helps and thank you very much!

 

[Gasman]

I am unable to download it for some reason.
If I try to open instead, I was able to extract the DB, but get 'Unrecognised database format' which is likely due to my only having 2007, yet I had a previous version of the DB as well?

So, someone else is going to have to chip in. Sorry.

 

[Sam Summers]

I am unable to download it for some reason.
If I try to open instead, I was able to extract the DB, but get 'Unrecognised database format' which is likely due to my only having 2007, yet I had a previous version of the DB as well?

So, someone else is going to have to chip in. Sorry.

Thank you for trying. Yes, just upgraded to 2019 recently

 

[bastanu]

Hi Sam,

The form gets the right value for the HazardID but the second form opens blank because of its record source is not updatable. You don't need all of those tables, just have a look at the update file. Please note that I also deleted the very tiny controls in the upper left, I suggest in the future you just set the Visible Property to No and the background to a color that reminds you that is a hidden control.

Cheers,
Vlad