NotInList Help

[docmeizie]

Basically what I need to do is properly code a NotInList event procedure using Access 97. What happens is I have a combobox that when you type the name in it matches and goes to that record. Well when there is not a record in the list what I want to do is add a whole new record but apparently from looking at Access help( reads like stereo instructions sometimes) you have to code an If...Then with certain set VBA parameters. Is there a way to over ride this or would I have to use the preset???? If you are wondering what I am talking about, look up the 97 Access example on NotInList.

If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmm

 

[KenReay]

Hi

You mean you want to add to the lsit without asking?

if so:

Private Sub Colors_NotInList(NewData As String, _
Response As Integer)
Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!Colors
' Prompt user to verify they wish to add new value.
Response = acDataErrAdded
ctl.RowSource = ctl.RowSource & ";" & NewData
set ctl = nothing
End Sub

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Hi

You mean you want to add to the lsit without asking?

if so:

Private Sub Colors_NotInList(NewData As String, _
Response As Integer)
Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!Colors
Response = acDataErrAdded
ctl.RowSource = ctl.RowSource & ";" & NewData
set ctl = nothing
End Sub

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Hi

You mean you want to add to the list without asking?

if so:

Private Sub Colors_NotInList(NewData As String, _
Response As Integer)
Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!Colors
Response = acDataErrAdded
ctl.RowSource = ctl.RowSource & ";" & NewData
set ctl = nothing
End Sub

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[docmeizie]

Well sort of. What I really want to do is add a complete new record when the NotInList occurs. I want it to override the error message that comes up and write a new record into the database: name, address, state, phone, age, bdate, parents name, jersey size, school, etc.

If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmm

 

[KenReay]

Hi

A combo box displays one column, where is it going to get the other columns you mention, do you want to display a form to capture this data and save it to a database table?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[daklem]

KenReay

I am looking to do just what you mention. I would like a form to open when the NotInList event occurs. The form would be based on a table that is also used to populate the original combo box, but the form would allow more data to be input which isn't included in the combo box.

 

[KenReay]

Hi

Create your combo box with limit to list set to true

then put code like so in the NotInListEvent

Private Sub strProduct_NotInList(NewData As String, Response As Integer)
DoCmd.OpenForm "frmGeneralMaintenance", acNormal, , , , acDialog, "Product " & ";" & NewData
Response = acDataErrAdded
End Sub

this is copied from one of my applications, so obviously you need to use your own form names etc
the key points are:

open the form as a modal form (acDialog) so that execution pauses until data entered

set the response to tell Access data has been added

pass the new data (such as you have) to the maintenance for via the OpenArgs parameter of the open Form command, in the maintenance form, on open event extract the OpenArgs data and use it in the maintenance form eg

If Len(Trim(Me.OpenArgs)) > 0 Then
If Mid(Me.OpenArgs, 13, 1) = ";" And Not IsNull(Mid(OpenArgs, 14)) Then
Set Rs = Me.RecordsetClone
With Rs
.AddNew
!strProduct = Mid(OpenArgs, 14)
!strProductDescription = Mid(OpenArgs, 14)
.Update
Me!frmProductType.Form.Bookmark = .LastModified
.Close
End With

again this is sample code taken from one of my applications, so you need to adjust it to suit, I include it just to give you the general idea

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[daklem]

Ken

The form opens in response to the NotInList event however the new data does not get added. If I then enter the data on the form myself and close the form, I get a messege to "choose an item from the list" (the new data is, however, in the list now because I entered it on the form)

Any thoughts?

Here is the code:

Private Sub Form_Load(Cancel As Integer)
Dim rst As Recordset

If Not IsNull(Me.OpenArgs) Then
Set rst = Me.RecordsetClone
rst.AddNew
rst![PhyLastName] = OpenArgs
rst.Update
Me!frmPhyEntry.Form.Bookmark = .LastModified
rst.Close
End If

End Sub


Thanks

 

[KenReay]

Hi

What code do you have in the notinlist event of the combo

In which form is the code you posted in last post

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[daklem]

This is the code in the main form

Private Sub Combo70_NotInList(NewData As String, Response As Integer)

DoCmd.OpenForm "frmPhyEntry", acNormal, , , , acDialog, NewData
Response = acDataErrAdded

End Sub

and the previous code is in the pop-up form.

 

[KenReay]

Hi

First puzzle is that data does not get added, have you tried putting a breakpoint on the code in the onload event and stepping thru the code to ensure it is executing the AddNew, and Update commands

Regards

Ken Reay

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[daklem]

Hi,

I'm a little unfamiliar with the debugging tools. Would you be able to walk me through the process?

 

[daklem]

Let me clarify things a little. I have two tables:

tblPatients

PatientID (PK) (auto number)
FirstName
LastName
PhysicianID (FK)

tblPhysicians

PhysicianID (PK) (auto number)
PhyFirstName
PhyLastName

There is a main form with based on tblPatients and a subform on the main form based on tblPhysicians. These are linked by PhysicianID. There is a combo box on the main form which uses tblPhysicians as look up values and assigns the tblPhysicians_PhysicianID to tblPatients_PhysicianID so the rest of the fields on the physician subform will be filled in when a physician is selected from the combo box. When the NotInList event of the combo box is triggered I would like a frmPhyEntry to open with the name the user types in the combo box to appear in the PhyLastName field and the user can enter the remainder of the data, close the form, and have the original subform be updated to the new Physicians information.

I hope this is clear.

 

[docmeizie]

Well back to my dilemma. Basically I have a form that takes information about the student. I just recently added to it the ability to search and see if a student is already in the database. When a student is not in the database I get the not in list error. This error I want to override completely and just enter the information that on the same form without having to open a new form. I just want it to add a new record so that I may enter all of the student's information.

If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmm

 

[KenReay]

Hi

How many people have the problem here

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[docmeizie]

Well I was the one with the original post, LOL. But I wasn't present over the weekend to add anymore to the original postings. It appears Daklem has the same problem though.

If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmm

 

[KenReay]

Hi

In the NotInList event of the combo box I have:

If MsgBox("Not in the List of Valid Warehouses, do you wish to add it?", _
vbYesNo, "Warning&quot = vbYes Then
DoCmd.OpenForm "frmWarehouses", acNormal, , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
Else
End If

The Combo box is set to limit to list = True, and the first column (ie the key column) is NOT hidden

In the called form (frmWarehouses) on open event I have:

Private Sub Form_Open(Cancel As Integer)
If IsNull(Me.OpenArgs) Then
Else
cmdNew_Click
strWarehouse = Left(Me.OpenArgs, 2)
End If
End Sub

and that works for me

would you like to compare notes with what you have?




Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PeteJohnston]

[/u]Ken[/u]
On a slightly sideways, you can actually display as many columns as you want in a combo box. Use a table or query with the columns you want to useas the row source, then change the column count to match the number of columns. You can change the width of each as well, so if you want to use some data but not necessarily display it you can set the column width to zero. You can access the data in each using

lstApproved.Column(x, lstApproved.ListIndex)

where x is the number of the column which corresponds to the data you want to use. The columns are zero-based so the first column is actually column 0.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[KenReay]

Hi

Yes, I know all that, but what has that got to do with adding data to the table on which a combo box is based, when the user enters a value which is not already in the list (Table) ?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK