Opening another form using double click in a combo box 1

[Mayhem9]

I have a form with a number of look-up combo boxes and I was using the following code the open a form that enters the data into the field that the look-up is referencing by double clicking on the combo box. Additionally, if you typed in an entry that wasn’t there you received an error message.

Private Sub ManID_DblClick(Cancel As Integer)
On Error GoTo Err_ManID_DblClick
    Dim lngManID As Long

    If IsNull(Me![ManID]) Then
        Me![ManID].Text = ""
    Else
        lngManID = Me![ManID]
        Me![ManID] = Null
    End If
    DoCmd.OpenForm "Manufacturer", , , , , acDialog, "GotoNew"
    Me![ManID].Requery
    If lngManID <> 0 Then Me![ManID] = lngManID

Exit_ManID_DblClick:
    Exit Sub

Err_ManID_DblClick:
    MsgBox Err.Description
    Resume Exit_ManID_DblClick
End Sub

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

I used this code in a database I built years ago (the one I am working on now is my second db) and I did so using look-ups in the table (I now know this is bad) and I also used an autonumber PK in every table. I prefer to use this code than have a number of buttons on the form to open the other forms, as it clutters and complicated the form.

I have recently removed the look-ups from the table and placed them in the form. Additionally, I deleted the autonumber PKs from the tables from which the look-ups reference. Additionally, I have implemented MajP’s code to turn my combo boxes to Find As You Type combo boxes (see thread702-1584791).

The code above refers to the list of Manufacturers, the table for which is as follows

Manufacturer
Man dbText PrimaryKey Indexed

When the code was working, the table was like this:


Manufacturer
ManID dbLong PrimaryKey Indexed
Man dbText


The form that contains the look-ups controls the following table:

Tool_Log
ToolID dbLong PrimaryKey Indexed
ManID dbText Indexed
Serial dbText Indexed
TypeID dbText Indexed
Size dbText Indexed
Description dbText
Set dbText Indexed
Year dbText Indexed
RRP dbCurrency
PurID dbText Indexed
Date dbDate
LocID dbText Indexed
ImagePath dbText

The look-up stored the selected data into a field called ManID in the main table that I use.

I knew that the initial issue would be that the autonumber PK in the Maufacturer table (ManID) no longer existed, yet the name ManID was present in the Tool_Log form/table.

I mad a copy of the database and then I tried replacing every instance of ManID with Man (in the code, the forms and tables). However, this didn’t work.

Any assistance is greatly appreciated.

 

[dhookom]

I would have left all of the Autonumbers in the tables but you have already worked hard enough on your structures. Since your PK is now text, you can't reference it as if it is still numeric. Try something like this.

Private Sub ManID_DblClick(Cancel As Integer)
  On Error GoTo Err_ManID_DblClick
    Dim strManID As String
    If Not IsNull(Me.ManID)
      strManID = Me![ManID]
    End If
 
    DoCmd.OpenForm "Manufacturer", , , , , acDialog, "GotoNew"
    Me![ManID].Requery
    If strManID <> "" Then
        Me![ManID] = strManID 
    End If

Exit_ManID_DblClick:
    Exit 

SubErr_ManID_DblClick:
    MsgBox Err.Description
    Resume Exit_ManID_DblClick
End Sub

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

Duane
Hook'D on Access
MS Access MVP

 

[Mayhem9]

Hi Duane,

Thanks for your help. I have a couple of lines of code that show up in red text in the VB Code window:

Private Sub ManID_DblClick(Cancel As Integer)
  On Error GoTo Err_ManID_DblClick
    Dim strManID As String    
    [COLOR=red]If Not IsNull(Me.ManID)[/color]
      strManID = Me![ManID]
    End If

    DoCmd.OpenForm "Manufacturer", , , , , acDialog, "GotoNew"
    Me![ManID].Requery
    If strManID <> "" Then
        Me![ManID] = strManID
     End If

Exit_ManID_DblClick:
    [COLOR=red]Exit [/color]

SubErr_ManID_DblClick:
    MsgBox Err.Description
    Resume Exit_ManID_DblClick
End Sub

Any idea why this is happening?

I would have left all of the Autonumbers in the tables but you have already worked hard enough on your structures.

This seems to be one of those issues on here - damned if you do and damned if you don't! I must admit though, by getting rid of the auto number it did make my database easier to incorporate another feature and whilst it stopped this one from working I think if I had to choose, I would pick the one that is working now. Not sure what I would have done if I had to have an Autonumber PK but that is hair pulling for another database!

Thanks again for your help,
Darren.

 

[dhookom]

Try change the red lines to:

'....
   If Not IsNull(Me!ManID) Then
'....
   Exit Sub
'....

Duane
Hook'D on Access
MS Access MVP

 

[Mayhem9]

Thanks Duane - that fixed the red text but I now get "Compile error: Label not defined" for

Private Sub ManID_DblClick(Cancel As Integer)
On Error GoTo Err_ManID_DblClick

The first line is highlighted yellow, the second is highlighted blue.

Cheers,
Darren

 

[dhookom]

The problem was caused by copy and paste in TT. You should learn how to fix my errors.

Try:

Private Sub ManID_DblClick(Cancel As Integer)
  On Error GoTo Err_ManID_DblClick
    Dim strManID As String    
    If Not IsNull(Me!anID) Then
      strManID = Me![ManID]
    End If

    DoCmd.OpenForm "Manufacturer", , , , , acDialog, "GotoNew"
    Me![ManID].Requery
    If strManID <> "" Then
        Me![ManID] = strManID
     End If

Exit_ManID_DblClick:
    Exit Sub

Err_ManID_DblClick:
    MsgBox Err.Description
    Resume Exit_ManID_DblClick
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Just noticed there was an error again which you should be able to catch. If you don't find it, post back for the answer.


Duane
Hook'D on Access
MS Access MVP

 

[Mayhem9]

The problem was caused by copy and paste in TT. You should learn how to fix my errors.

Settle down Tiger, I have enough trouble trying to fix my own errors

I spotted it (and the second one you threw in to test me). The problem is that the code might as well be ancient hieroglyphics but I did notice that the missing sub that cased the second red text error had slipped a line and was creating the compile error

Thanks for your help Duane - the double click to open the associated form works perfectly now. However, the not in list bit isn't playing nice, as I can type in anything and it will let me move on to the next field.

Cheers,
Darren

 

[dhookom]

What are the significant properties of the combo box?

Duane
Hook'D on Access
MS Access MVP

 

[Mayhem9]

What are the significant properties of the combo box?

Hi Duane,

I hope that the following might be what you are after:

The combo box is a find as you type combo box as outlined in thread702-1584791. It selects a string from a table (where the string is the PK):

Name: ManID
Control Source: ManID
Row Source Type: Table /Query
Row source: SELECT Manufacturer.Man FROM Manufacturer ORDER BY [Man];
Column Count: 1
Bound to Column: 1
Limit To List: No

I changed this to yes, thinking this was the problem. When I tried to enter something not in the list it returned the following error "91 Object variable or With block variable not set". After clicking OK, it then returned the error message that it is suppose to.

Auto Expand: Yes
IME Hold: No
IME Mode: No Control
IME Sentence Mode: None
Visible: Yes
Display When: Always
Enabled: Yes
Locked: No
Allow Auto Control: Yes

Thanks,
Darren

 

[dhookom]

This was your issue:
[tt]Limit To List: No[/tt]
Then change the NotInList code to see what's happening. Try a bunch of stuff on your own and report back.

Duane
Hook'D on Access
MS Access MVP

 

[Mayhem9]

The Row Source was blank when I looked again. I must have cut it when pasting into here and not noticed. Now it is back I no longer get the compile error and the NotInList error appears instead.

Thanks Duane.

Darren

 

[Mayhem9]

Hi Duane,

I wonder if you would be willing to give me some further help with this code.

  On Error GoTo Err_cmbMan_DblClick
    Dim strcmbMan As String    
    If Not IsNull(Me!cmbMan) Then
      strcmbMan = Me![cmbMan]
    End If

    DoCmd.OpenForm "Manufacturer", , , , , acDialog, "GotoNew"
    Me![cmbMan].Requery
    If strcmbMan <> "" Then
        Me![cmbMan] = strcmbMan
     End If

Exit_cmbMan_DblClick:
    Exit Sub

Err_cmbMan_DblClick:
    MsgBox Err.Description
    Resume Exit_cmbMan_DblClick

In trying to improve my game MajP advised me against giving the combo box the same name as the control source (in this case ManID). As such, I changed the name of the combo box to cmbMan. Now I cannot figure out which "cmdMan" to change to "ManID". I have tried several combinations and I either get an error or the form opens (as it should) but #Name?

I'm pretty sure that "strcmdMan" should be changed to "strManID" but I really am just guessing here.

Any pointers are gratefully appreciated.

Cheers,
Darren

 

[dhookom]

I either get an error or the form opens (as it should) but #Name?

What error? Where do you see #Name?

Nothing in your code looks wrong other than most old Access programmers would use "cboMan" rather than "cmbMan". You did spell it "cmdMan" in your message which I assume is a typo.

I expect there might be an issue in the code running in the form Manufacturer.

Duane
Hook'D on Access
MS Access MVP

 

[Mayhem9]

Thanks Duane,

Bugger - my fault but your reply led me to the problem.

For some reason, I had not changed the Manufacturer form when I deleted the previous PK. The field is actually Man, not ManID (ManID was the PK). I have just rectified this and replaced ManID with Man in the code I posted and it works perfectly.

I had figured that I had screwed something up in the code and not that I had screwed up in the table. I have had rebuild the database and import the tables, forms etc and I think I may have imported an older version of the form. One of the other was also incorrect.

Cheers,
Darren.