Combo Box Not In List Help

  • Thread starter mattc66 via AccessMonster.com
  • Start date
M

mattc66 via AccessMonster.com

Hi All,

I have a combo box that I'd like to use 2 ways.

1) Look Up Data in external table and populate form with data found. I got
this to work just fine.

Its the second way that I need help with:
2) If the data typed is not found in Combo Box I'd like to check the table
associated with main form and see if the it already exsists. If it finds the
data then it would take the user to that record.

See the code I placed in my NOT IN LIST event it doesn't seem to work. I get
a message "INVALID USE OF NULL"

Private Sub LookUpOrder_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_LookUpOrder_NotInList

'Message box warning that order doesn't exsist.

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.LookUpOrder.Value
stLinkCriteria = "[OrderNum]=" & "'" & SID & "'"


'Check table for for item number.
If DCount("OrderNum", "tblShip", stLinkCriteria) >= 1 Then

'Go to record of original Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark

Else
Msgbox "Order Number" _
& SID & " is not valid order number," _
& vbCr & vbCr & "check the number and try again." _
& vbCr & vbCr & "If the order is still not found" _
& vbCr & vbCr & "contact order entry.", vbExclamation _
, "ORDER NOT FOUND"

End If
Me.TrackingNum.SetFocus

Set rsc = Nothing
Me.LookUpOrder = Null

Exit_LookUpOrder_NotInList:
Exit Sub

Err_LookUpOrder_NotInList:
Msgbox Err.Description
Resume Exit_LookUpOrder_NotInList

End Sub
 
G

GH

I am just guessing that the actual line generating this error is
Me.LookUpOrder = Null, right? Since LookUpOrder appears to be a
control on your form, you can't set it to Null at runtime. Instead,
try setting the SelectedItem to -1 (no item selected) or the Value to
an empty string (""). I don't know for sure that your code here
ultimately provides the result you need for your second requirement,
but the changed line should get you passed your error.

- GH
 
M

Marshall Barton

mattc66 said:
I have a combo box that I'd like to use 2 ways.

1) Look Up Data in external table and populate form with data found. I got
this to work just fine.

Its the second way that I need help with:
2) If the data typed is not found in Combo Box I'd like to check the table
associated with main form and see if the it already exsists. If it finds the
data then it would take the user to that record.

See the code I placed in my NOT IN LIST event it doesn't seem to work. I get
a message "INVALID USE OF NULL"

Private Sub LookUpOrder_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_LookUpOrder_NotInList

'Message box warning that order doesn't exsist.

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.LookUpOrder.Value
stLinkCriteria = "[OrderNum]=" & "'" & SID & "'"


'Check table for for item number.
If DCount("OrderNum", "tblShip", stLinkCriteria) >= 1 Then

'Go to record of original Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark

Else
Msgbox "Order Number" _
& SID & " is not valid order number," _
& vbCr & vbCr & "check the number and try again." _
& vbCr & vbCr & "If the order is still not found" _
& vbCr & vbCr & "contact order entry.", vbExclamation _
, "ORDER NOT FOUND"

End If
Me.TrackingNum.SetFocus

Set rsc = Nothing
Me.LookUpOrder = Null
Click to expand...
[]

I think at least part of the problem is when the combo box
does not have a value before you enter a new, not in list
item. Your code uses
SID = Me.LookUpOrder.Value
but the new value has not yet been established because it's
not in the list. Since the previous Value is still in
effect, that line will fail because a string variable can
not contain Null.

In the Not In List event, you need to use the NewData
argument to refer to the text the user entered:
SID = NewData

You may gave a second issue if the combo box is bound to a
field in the form's record source table/query. Your two
different objectives can clash. How can you tell if a user
is searching for a record or trying to enter data in the
current record (destroying the previous value).
 
M

mattc66 via AccessMonster.com

That worked.... However I had to set the LIMIT TO LIST to no.

Thank you

Marshall said:
I have a combo box that I'd like to use 2 ways.
Click to expand...
[quoted text clipped - 44 lines]
Set rsc = Nothing
Me.LookUpOrder = Null
Click to expand...
[]

I think at least part of the problem is when the combo box
does not have a value before you enter a new, not in list
item. Your code uses
SID = Me.LookUpOrder.Value
but the new value has not yet been established because it's
not in the list. Since the previous Value is still in
effect, that line will fail because a string variable can
not contain Null.

In the Not In List event, you need to use the NewData
argument to refer to the text the user entered:
SID = NewData

You may gave a second issue if the combo box is bound to a
field in the form's record source table/query. Your two
different objectives can clash. How can you tell if a user
is searching for a record or trying to enter data in the
current record (destroying the previous value).
Click to expand...
 
M

Marshall Barton

You might want to avoid the Not In List event and use the
combo box's AfterUpdate event instead:

If Me.LookUpOrder.ListIndex =-1 Then
' data is not in the list
' use the combo box's Value instead of NewData
. . .
Else
'data is in the list
. . .
End If
--
Marsh
MVP [MS Access]

That worked.... However I had to set the LIMIT TO LIST to no.

Marshall said:
I have a combo box that I'd like to use 2 ways.
Click to expand...
[quoted text clipped - 44 lines]
Set rsc = Nothing
Me.LookUpOrder = Null
Click to expand...
[]

I think at least part of the problem is when the combo box
does not have a value before you enter a new, not in list
item. Your code uses
SID = Me.LookUpOrder.Value
but the new value has not yet been established because it's
not in the list. Since the previous Value is still in
effect, that line will fail because a string variable can
not contain Null.

In the Not In List event, you need to use the NewData
argument to refer to the text the user entered:
SID = NewData
Click to expand...
Click to expand...
 
M

mattc66 via AccessMonster.com

What if its not in the list and its not found in the table what would my if
statement be

If me.lookuporder.listindex = 0 then?

Marshall said:
You might want to avoid the Not In List event and use the
combo box's AfterUpdate event instead:

If Me.LookUpOrder.ListIndex =-1 Then
' data is not in the list
' use the combo box's Value instead of NewData
. . .
Else
'data is in the list
. . .
End If
That worked.... However I had to set the LIMIT TO LIST to no.
Click to expand...
[quoted text clipped - 17 lines]
Click to expand...
 
M

mattc66 via AccessMonster.com

Here is my code in the AfterUpdate event.

It's very close to working - The only issue I am having is if it's in the
combo box list, and it's already been added. Then when you go back and type
in a order that is in both the list and already added it trys to add it again.
It fails because that would create a duplicate. What I wanted it todo was to
go-to the record if found.

Private Sub LookUpOrder_AfterUpdate()
On Error GoTo Err_LookUpOrder_Click

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.LookUpOrder.Value
stLinkCriteria = "[OrderNum]=" & "'" & SID & "'"


'Check table for for item number.
If Me.LookUpOrder.ListIndex = -1 Then
'Data is not in the list
If DCount("OrderNum", "tblShip", stLinkCriteria) >= 1 Then

'Go to record of original Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark

Me.LookUpOrder.SetFocus
Else
'Message box warning that part number doesn't exsist.

Msgbox "Order Number" _
& SID & " is not valid order number," _
& vbCr & vbCr & "check the number and try again." _
& vbCr & vbCr & "If the order is still not found" _
& vbCr & vbCr & "contact order entry.", vbExclamation _
, "ORDER NOT FOUND"

Me.LookUpOrder.SetFocus
End If
Else
DoCmd.GoToRecord , , acNewRec
'This is suposed to move curser to the subform.
'Me!frmInventoryInput.Form!subFRMqryCOUNT.Form!PHYSICAL
'Docmd.
Me.OrderNum = Me.LookUpOrder.Column(0)
Me.CustNo = Me.LookUpOrder.Column(1)
Me.CustomerPO = Me.LookUpOrder.Column(3)
Me.ShipToName = Me.LookUpOrder.Column(5)
Me.ShipToAddress1 = Me.LookUpOrder.Column(6)
Me.ShipToAddress2 = Me.LookUpOrder.Column(7)
Me.ShipToCity = Me.LookUpOrder.Column(8)
Me.ShipToState = Me.LookUpOrder.Column(9)
Me.ShipToZip = Me.LookUpOrder.Column(10)
Me.Phone = Me.LookUpOrder.Column(11)
Me.ContactName = Me.LookUpOrder.Column(12)
Me.cboCarrier = Me.LookUpOrder.Column(13)
Me.BILLOPT = Me.LookUpOrder.Column(14)
Me.COMPANY = Me.LookUpOrder.Column(19)
Me.Refresh

Me.TrackingNum.SetFocus

End If


Set rsc = Nothing
Me.LookUpOrder = Null

Exit_LookUpOrder_Click:
Exit Sub

Err_LookUpOrder_Click:
Msgbox Err.Description
Resume Exit_LookUpOrder_Click

End Sub


Marshall said:
You might want to avoid the Not In List event and use the
combo box's AfterUpdate event instead:

If Me.LookUpOrder.ListIndex =-1 Then
' data is not in the list
' use the combo box's Value instead of NewData
. . .
Else
'data is in the list
. . .
End If
That worked.... However I had to set the LIMIT TO LIST to no.
Click to expand...
[quoted text clipped - 17 lines]
Click to expand...
 
M

Marshall Barton

mattc66 said:
Here is my code in the AfterUpdate event.

It's very close to working - The only issue I am having is if it's in the
combo box list, and it's already been added. Then when you go back and type
in a order that is in both the list and already added it trys to add it again.
It fails because that would create a duplicate. What I wanted it todo was to
go-to the record if found.

Private Sub LookUpOrder_AfterUpdate()
On Error GoTo Err_LookUpOrder_Click

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.LookUpOrder.Value
stLinkCriteria = "[OrderNum]=" & "'" & SID & "'"


'Check table for for item number.
If Me.LookUpOrder.ListIndex = -1 Then
'Data is not in the list
If DCount("OrderNum", "tblShip", stLinkCriteria) >= 1 Then

'Go to record of original Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark

Me.LookUpOrder.SetFocus
Else
'Message box warning that part number doesn't exsist.

Msgbox "Order Number" _
& SID & " is not valid order number," _
& vbCr & vbCr & "check the number and try again." _
& vbCr & vbCr & "If the order is still not found" _
& vbCr & vbCr & "contact order entry.", vbExclamation _
, "ORDER NOT FOUND"

Me.LookUpOrder.SetFocus
End If
Else
DoCmd.GoToRecord , , acNewRec
'This is suposed to move curser to the subform.
'Me!frmInventoryInput.Form!subFRMqryCOUNT.Form!PHYSICAL
'Docmd.
Me.OrderNum = Me.LookUpOrder.Column(0)
Me.CustNo = Me.LookUpOrder.Column(1)
Me.CustomerPO = Me.LookUpOrder.Column(3)
Me.ShipToName = Me.LookUpOrder.Column(5)
Me.ShipToAddress1 = Me.LookUpOrder.Column(6)
Me.ShipToAddress2 = Me.LookUpOrder.Column(7)
Me.ShipToCity = Me.LookUpOrder.Column(8)
Me.ShipToState = Me.LookUpOrder.Column(9)
Me.ShipToZip = Me.LookUpOrder.Column(10)
Me.Phone = Me.LookUpOrder.Column(11)
Me.ContactName = Me.LookUpOrder.Column(12)
Me.cboCarrier = Me.LookUpOrder.Column(13)
Me.BILLOPT = Me.LookUpOrder.Column(14)
Me.COMPANY = Me.LookUpOrder.Column(19)
Me.Refresh

Me.TrackingNum.SetFocus

End If


Set rsc = Nothing
Me.LookUpOrder = Null

Exit_LookUpOrder_Click:
Exit Sub

Err_LookUpOrder_Click:
Msgbox Err.Description
Resume Exit_LookUpOrder_Click

End Sub
Click to expand...


If a user enters something that is in the list, your code
adds a new record using the data from the list item. You
have nothing in there to prevent the same record from being
added multiple times.

Are you asking how to prevent that? If so, add some code to
check if the order already exists (using another DCount??)
before going to a new record.
 
M

mattc66 via AccessMonster.com

Just to be clear on what I am trying to occomplish:

I have 3 possible issues with the combo box.
1) Order# in cboBox, but not yet added to the tblShip
2) Order# in cboBox and already added to the tblShip
3) Order# not in cboBox and not in the tblShip (wrong # typed)

So the following Code is how I intended to address these 3 issues, but it's
still not working the way I had hoped. It's adding order# that meet the 3rd
issues when it should be displaying the message box.

2) Order# in cboBox and already added to the tblShip
'Checking cboList and the Order# is not found
If Me.LookUpOrder.ListIndex = -1 Then

'Check tblShip
If DCount("OrderNum", "tblShip", stLinkCriteria) >= 1 Then

'If it finds the Order# then go to that record.
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark

1) Order# in cboBox, but not yet added to the tblShip
Else
If DCount("OrderNum", "tblShip", stLinkCriteria) = 0 Then

DoCmd.GoToRecord , , acNewRec

Me.OrderNum = Me.LookUpOrder.Column(0)
Me.CustNo = Me.LookUpOrder.Column(1)
Me.CustomerPO = Me.LookUpOrder.Column(3)
Me.ShipToName = Me.LookUpOrder.Column(5)
Me.ShipToAddress1 = Me.LookUpOrder.Column(6)
Me.ShipToAddress2 = Me.LookUpOrder.Column(7)
Me.ShipToCity = Me.LookUpOrder.Column(8)
Me.ShipToState = Me.LookUpOrder.Column(9)
Me.ShipToZip = Me.LookUpOrder.Column(10)
Me.Refresh

3) Order# not in cboBox and not in the tblShip (wrong # typed)
Else
'Message box warning that part number doesn't exsist.

Msgbox "Order Number" _
& SID & " is not valid order number," _
& vbCr & vbCr & "check the number and try again." _
& vbCr & vbCr & "If the order is still not found" _
& vbCr & vbCr & "contact order entry.", vbExclamation _
, "ORDER NOT FOUND"

Me.LookUpOrder.SetFocus

End If


Marshall said:
Here is my code in the AfterUpdate event.
Click to expand...
[quoted text clipped - 76 lines]
End Sub
Click to expand...

If a user enters something that is in the list, your code
adds a new record using the data from the list item. You
have nothing in there to prevent the same record from being
added multiple times.

Are you asking how to prevent that? If so, add some code to
check if the order already exists (using another DCount??)
before going to a new record.
Click to expand...
 
M

Marshall Barton

mattc66 said:
Just to be clear on what I am trying to occomplish:

I have 3 possible issues with the combo box.
1) Order# in cboBox, but not yet added to the tblShip
2) Order# in cboBox and already added to the tblShip
3) Order# not in cboBox and not in the tblShip (wrong # typed)

So the following Code is how I intended to address these 3 issues, but it's
still not working the way I had hoped. It's adding order# that meet the 3rd
issues when it should be displaying the message box.

2) Order# in cboBox and already added to the tblShip
'Checking cboList and the Order# is not found
If Me.LookUpOrder.ListIndex = -1 Then

'Check tblShip
If DCount("OrderNum", "tblShip", stLinkCriteria) >= 1 Then

'If it finds the Order# then go to that record.
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark

1) Order# in cboBox, but not yet added to the tblShip
Else
If DCount("OrderNum", "tblShip", stLinkCriteria) = 0 Then

DoCmd.GoToRecord , , acNewRec

Me.OrderNum = Me.LookUpOrder.Column(0)
Me.CustNo = Me.LookUpOrder.Column(1)
Me.CustomerPO = Me.LookUpOrder.Column(3)
Me.ShipToName = Me.LookUpOrder.Column(5)
Me.ShipToAddress1 = Me.LookUpOrder.Column(6)
Me.ShipToAddress2 = Me.LookUpOrder.Column(7)
Me.ShipToCity = Me.LookUpOrder.Column(8)
Me.ShipToState = Me.LookUpOrder.Column(9)
Me.ShipToZip = Me.LookUpOrder.Column(10)
Me.Refresh

3) Order# not in cboBox and not in the tblShip (wrong # typed)
Else
'Message box warning that part number doesn't exsist.

Msgbox "Order Number" _
& SID & " is not valid order number," _
& vbCr & vbCr & "check the number and try again." _
& vbCr & vbCr & "If the order is still not found" _
& vbCr & vbCr & "contact order entry.", vbExclamation _
, "ORDER NOT FOUND"

Me.LookUpOrder.SetFocus

End If
Click to expand...


That code is missing an End If somewhere.

I thnk your logic should be more like:

If Me.LookUpOrder.ListIndex = -1 Then
'Order# is not found in cboList
If DCount("OrderNum", "tblShip", stLinkCriteria) >= 1 Then
'2) Order# not in cboBox but it is in tblShip
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark

Else '3) Order# not in cboBox and not in the tblShip
'Warning: part number does not exist.
Msgbox "Order Number" _
. . .
END IF
Else
If DCount("OrderNum", "tblShip", stLinkCriteria) = 0 Then
'1) Order# in cboBox, but not in tblShip
DoCmd.GoToRecord , , acNewRec

Me.OrderNum = Me.LookUpOrder.Column(0)
. . .
Else '4) Order# in cboBox AND in tblShip
' ? ? ?
End If
End If
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DCount LookUp Issue 5
Data Type Mismatch in criteria 2
Preventing Duplicates from being entered 3
Problem with code to catch duplicate Primay Key 22
Duplicate Values 3
If duplicate, open a different form 1
Checking multiple fields for Duplicate 18
How to Clear acFormAdd Filter 3

Top