form help 1

[BabyPowder2u]

I have a form which has a cboPOC field. Through the MS reservations? database I got the procedure to allow entering a new POC in the POC table by DblClicking POC field. This works fine with one caviat: the field name has to be the same as the Control Source. if I change the field name to prefix "cbo" to indicate it is a cbobox, when I "X" the add POC form, I get the error "object doesn't support this property or method". I would like to be able to call the field cboPOC to indicate what it is.

The code for the POC events are:

Private Sub POCID_DblClick(Cancel As Integer)

On Error GoTo Err_POCID_DblClick
Dim lngPOCID As Long

If IsNull(Me![POCID]) Then
Me![POCID].Text = ""
Else
lngPOCID = Me![POCID]
Me![POCID] = Null
End If
DoCmd.OpenForm "frmPOCDblClk", , , , , acDialog, "GotoNew"
Me![POCID].Requery
If lngPOCID <> 0 Then Me![POCID] = lngPOCID
Forms!frmPOC!POCID = Me![POCID]


Exit_POCID_DblClick:
Exit Sub

Err_POCID_DblClick:
MsgBox Err.Description
Resume Exit_POCID_DblClick

End Sub

Private Sub POCID_NotInList(NewData As String, Response As Integer)
MsgBox "Double-click this field to add an entry to the list."
Response = acDataErrContinue
End Sub

the form load for frmPOC is:
Private Sub Form_Load()
If Me.OpenArgs = "GotoNew" And Not IsNull(Me![POCID]) Then
DoCmd.DoMenuItem acFormBar, 3, 0, , acMenuVer70
End If
End Sub

Any help would be appreciated.
Thanks,
T

 

[Frink]

Hallo,

I assume cboPOCID is an integer and LimitToList is True

Private Sub cboPOCID_DblClick(Cancel As Integer)
On Error GoTo lblErr
    Dim lngPOCID As Long
    lngPOCID = val(nz(Me!cboPOCID,""))
    DoCmd.OpenForm "frmPOC", , , , , acDialog, "GotoNew"
    Me!cboPOCID.Requery
    If lngPOCID <> 0 Then Me!cboPOCID = lngPOCID
lblExit:
    Exit Sub
lblErr:
    MsgBox Err.Description
    Resume lblExit
End Sub

Private Sub cboPOCID_NotInList(NewData As String, Response As Integer)
    MsgBox "Double-click this field to add an entry to the list."
    Response = acDataErrContinue
End Sub

Private Sub Form_Load()
    If Nz(Me.OpenArgs,"") = "GotoNew" Then DoCmd.GoToRecord ,,acNewRec
End Sub

- Frink

 

[BabyPowder2u]

Thank you very much Frink,

That took care of my problem nicely.

T

 

[BabyPowder2u]

I have implimented the changes. I have however come across 1 problem.

I also used it on an "Office Name" field
if the Officefield is empty then is dblclicked & the Office form opened to add, user clicks "X" w/o adding a new record, office form closes & returns to officefield with field still blank (works fine).

If the Officefield has data, it is deleted, then the office field is dblclicked..office form opens.. if at this point the user changes their mind & doesn't enter a new Office but simply closes the form, an error occurs:
"You must save the current field before you run the query action, OK"

The same thing happens to the POC field if it is initially filled, then deleted, dblclicked & form closed w/no entry it produces the same error.
My actual code is: (some names were referred to incorrectly in my earlier post)

mainform:
Option Compare Database
Option Explicit

Private Sub cboEmPOC_NotInList(NewData As String, Response As Integer)
MsgBox "Double-click this field to add an entry to the list."
Response = acDataErrContinue
End Sub

Private Sub cboEmPOC_DblClick(Cancel As Integer)
On Error GoTo lblErr
Dim lngEMPOC As Long
lngEMPOC = Val(Nz(Me!cboEmPOC, ""))
DoCmd.OpenForm "frmPOCDblClk", , , , , acDialog, "GotoNew"
Me!cboEmPOC.Requery
If lngEMPOC <> 0 Then Me!cboEmPOC = lngEMPOC
lblExit:
Exit Sub
lblErr:
MsgBox Err.Description
Resume lblExit

End Sub

frmPOCdblClk:
Private Sub Form_Load()
If Nz(Me.OpenArgs, "") = "GotoNew" Then
DoCmd.GoToRecord , , acNewRec
End Sub

 

[Frink]

Hallo,

What's the rest of the code in frmPOCdblClk?

It's difficult to see where the error is as I can't see the form or which line it fails on.
You could put a RunCommand acCmdSaveRecord before the Requery statement.

Also a minor, but important layout comment. In Form_Load, the 'DoCmd.GoToRecord , , acNewRec' should be on the preceding line, after the 'Then'. It makes no difference to the machine, but it can make it confusing to the human, if left how it is.

- Frink

 

[BabyPowder2u]

Hello Frink,

The only other code in frmPOCDblClk is an identical event
for
Private Sub cboOffice_DblClick(Cancel As Integer):
it contains the exact same code as the _dblClick above, just checking for office symbol (that fails under the same circumstances)

the If Nz(....... Then docmd is actually on one line in the code, the wordwrap here dropped the line down, otherwise it would have failed the editor.

There are only 2 query actions, if you want to call it that:
1. the cbo is created by a query
2. the Me!cboEmPOC.Requery

Since the creation of the cbo has always worked fine, I don't think there is anything wrong with that query.
There is something that has to do with the field contents being deleted, then dblclicking to get the frmPOCdblClk form and then closing it with no changes. and since everything works fine except when the field has an initial value & it is deleted then fails that seems to be the key. before changing the code to what you gave me to accomodate the cbo name, if I followed the same actions, deleted the field, dblclicked, closed frmPOCdblClk w/no changes...
the field would be "restored" to the value in it before the dblclick event occured, but no error would occur. I would prefer that the field were to remain empty... but either is better than an error.

 

[Frink]

Hallo,

How about saving the record before opening the form?
The Error is about Saving the Field. Not coma across that one before. Maybe it's in the state of having changed, but not updated. Maybe setting the focus to another control would save the field.

- Frink