Have a combobox that I don't want to act like a Combobox (1 Viewer)

[hardhitter06]

Hi,

I have a DepartmentNumber field on my InputForm (for tblMain) that is a combobox with department numbers. This combo box looks up tblDept.

I also have "On Not in List" code for this DepartmentNumber field so that if a user enters a department number that doesn't exist, a sub form opens which forces them to enter a new department number and once they save, both forms close and the user starts from the beginning and now the new Department number is in the list. ..that works perfect.

I've done research and learned that you can't hide the "arrow" on the combobox. This is what I'm asking help on. I don't want the user to be able to use the drop down list to make a selection because it increases the chances of making an error with a wrong selection. I'd perfer them to type in the 8 digit number and if the number is in the table then they can move on to the next field but if not, my On Not in List code takes effect.

Is there a way to accomplish this (not allowing the drop down feature)?

Thank you.

 

[spikepl]

Easy. Don't use a combobox, since you do not wish to make use of its salient feature.

 

[hardhitter06]

But I need something to reference tblDept.

 

[vbaInet]

Use a listbox.

 

[hardhitter06]

A listbox isnt the answer either...

I need the combobox functionality without the drop down component...easy fix might be to cover up the drop down arrow with a label but everyone in my office has different resolutions therefore part of the arrow might still show depending...plus thats the lazy way of doing things.

I just need someone with some expertise to guide me in a better direction (if what i'm asking for is possible).

Thank you

 

[boblarson]

A listbox isnt the answer either...

I need the combobox functionality without the drop down component...easy fix might be to cover up the drop down arrow with a label but everyone in my office has different resolutions therefore part of the arrow might still show depending...plus thats the lazy way of doing things.

I just need someone with some expertise to guide me in a better direction (if what i'm asking for is possible).

Thank you

What combo box functionality do you need that doesn't include the drop down? I'm not understanding what it is about the combo box without the drop down that a text box can't fulfill. If it is the Not In List, that is easy to replicate if you use a DLookup in the Before Update event of the text box.

 

[hardhitter06]

Bob,

Yes, I must be mistaken then.

Your idea would work but could you help me write that BeforeUpdate please?

I don't know what info you need but this is my row source for InventoryDepartmentNumber

 SELECT tblDept.DepartmentID, tblDept.InventoryDepartmentNumber FROM tblDept ORDER BY tblDept.InventoryDepartmentNumber;

Not Not in List code:

Private Sub InventoryDepartmentNumber_NotInList(NewData As String, Response As Integer)
 Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmAddDepartment"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

And I need this input mask: 00000000;;*

And Text78 as the new textbox

Once I have this as a reference I'll be able to mirror it for any future databases...Thank You

 

[boblarson]

Something like this.

Private Sub InventoryDepartmentNumber_BeforeUpdate(Cancel As Integer)
   If DCount("*", "tblDept", "InventoryDepartmentNumber=" & Me.InventoryDepartmentNumber) = 0 Then
    If Msgbox("This Inventory Department Number does not exist.  Do you wish to add it?, vbQuestion + vbYesNo, "Add Department Number?") = vbYes Then
       DoCmd.OpenForm "FormNameHere", DataMode:=acFormOpenDataMode, WindowMode:=acDialog
    Else
        Cancel = True
        Me.InventoryDepartmentNumber.Undo
   End If    
End If

 

[hardhitter06]

Bob,

I'm getting an error on the Msgbox line regardless if I enter one that exists or doesn't exist.

Here is the code (changed the textbox and control source to the name "InventoryDepartmentNumber"

 Private Sub InventoryDepartmentNumber_BeforeUpdate(Cancel As Integer)
 If DCount("*", "tblDept", "InventoryDepartmentNumber=" & Me.InventoryDepartmentNumber) = 0 Then
    If Msgbox("This Inventory Department Number does not exist.  Do you wish to add it?, vbQuestion + vbYesNo, "Add Department Number?") = vbYes Then
       DoCmd.OpenForm frmAddDepartment, DataMode:=AcFormOpenDataMode, WindowMode:=acDialog
    Else
        Cancel = True
        Me.InventoryDepartmentNumber.Undo
   End If
End If
End Sub

Do i also need the not in list code for this textbox ? (I dont believe so)

 

[boblarson]

Bob,

I'm getting an error on the Msgbox line regardless if I enter one that exists or doesn't exist.

Here is the code (changed the textbox and control source to the name "InventoryDepartmentNumber"

 Private Sub InventoryDepartmentNumber_BeforeUpdate(Cancel As Integer)
 If DCount("*", "tblDept", "InventoryDepartmentNumber=" & Me.InventoryDepartmentNumber) = 0 Then
    If Msgbox("This Inventory Department Number does not exist.  Do you wish to add it?, vbQuestion + vbYesNo, "Add Department Number?") = vbYes Then
       DoCmd.OpenForm frmAddDepartment, DataMode:=AcFormOpenDataMode, WindowMode:=acDialog
    Else
        Cancel = True
        Me.InventoryDepartmentNumber.Undo
   End If
End If
End Sub

Do i also need the not in list code for this textbox ? (I dont believe so)

Sorry, forgot to include a quote after the question mark in the message box code:
If Msgbox("This Inventory Department Number does not exist. Do you wish to add it?", vbQuestion + vbYesNo, "Add Department Number?") = vbYes Then

And the not in list is handled by the Dcount code here and opening the add Department form if the person says they want to add it.

 

[hardhitter06]

Bob,

Now getting the error "Compile error: Expected variable or procedure, not module"

Its pointing to:

 DataMode:=AcFormOpenDataMode,

I think it needs another piece of code following this..

 

[hardhitter06]

I changed it to:

 [DataMode As AcFormOpenDataMode = acFormPropertySettings], [WindowMode As AcWindowMode = acWindowNormal]

Which accepts Dept numbers in the table.

If its a new department number, will NOW prompt me yes or no (didnt have this before)

If I say No, the entry is deleted and we are good.

If I say Yes, I get a debug error ("Run-time error '2465': Missing Asset DB can't find field '|1' referred to in your expression") and it points me to this line:

 DoCmd.OpenForm frmAddDepartment, [DataMode As AcFormOpenDataMode = acFormPropertySettings], [WindowMode As AcWindowMode = acWindowNormal]

 

[boblarson]

Sorry, I had one part off. But change this:

DoCmd.OpenForm frmAddDepartment, [DataMode As AcFormOpenDataMode = acFormPropertySettings], [WindowMode As AcWindowMode = acWindowNormal]

to this:

DoCmd.OpenForm "frmAddDepartment", DataMode:=acFormAdd, WindowMode:= acWindowNormal

 

[hardhitter06]

Yes that does what its suppose to, thank you bob!

There is one small detail that I would like to change...

When the AddDepartment form opens, the InventoryNumber field equals "0" and the "0" is highlighted in black...anyway for that to not have any value in that field (in other words keep it blank)?

 

[vbaInet]

Is the field a calculation or perhaps you have the Default Value property of the field set to 0. Look in the property of the field in the table or the property of the control that is bound to the field.

 

[hardhitter06]

VBA,

That was it...thanks for the help!

 

[vbaInet]

Glad we could help hardhitter.

 

[hardhitter06]

So I was thinking, I have this field set up so if the user enters a new Department Number, the Yes/No dialog box opens asking if you want to add the number.

When I hit yes, it opens the form (frmAddDepartment) where I can add this number in the InventoryDepartmentNumber field.

Is it possible to have the InventoryDepartmentNumber field (within the frmAddDepartment) flood from the number entered in the previous form (frmInput)?

 Forms!frmAddDepartment!me.InventoryDepartmentNumber = Me.InventoryDepartmentNumber

I used similar code (on a different DB) within an OnClick of a button to show on the new form what someone searched on the previous form. But it was like Text 1 = Text 3...it didn't have the same name.

Also, there is no button to click since a missing Department Number triggers the Yes/No box so even if this code was correct, i don't think it would work the same...?

 

[boblarson]

First off, you have ME in the form path on the left side. That is wrong. Nothing ever goes on the left side of me.

So, Forms!frmAddDepartment.InventoryDepartmentNumber

If the form you are opening to add the department is what you want the department number to be in the text box, I believe you would have it if you got rid of the .me portion that you erroneously included. I can't tell for sure because I don't know your field names nor your control names.

 

[hardhitter06]

Bob,

I have a form called frmInput with the field/control name InventoryDepartmentNumber that feeds tblMain.

If a user enters a department number not in tblMain (your earlier code), the form frmAddDepartment opens with the field/control name InventoryDepartmentNumber which will then add into tblDept

Within tblMain, InventoryDepartmentNumber has a relationship with tblDept's InventoryDepartmentNumber.

So frankly I need the number from frmInput to flood into frmAddDepartment if the number doesn't already exist in tblDepartment lol...sounds confusing - i hope i didnt lose you.

Does this make sense?

So basically, whatever was entered in frmInput for DepartmentNumber will need to flood into frmAddDepartment if that AddDepartment form is triggered