Best Way To Do This?

[Chaster]

With a combobox I want to search for a companies address and if found go to
that record by using the [CompanyId] field.

I have that working ok.

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CompanyID] = " & Str(Me![Combo35])
Me.Bookmark = rs.Bookmark
Me.Rest.SetFocus

If the data is not found I want to enter a new record and insert the data
that was just typed into the combox into the [BusinessStreet] field in the
new record and set the focus to another control.

I have tried this on the after update of the not in list property but it is
not working.

Basically it would be something like

If (the data I just typed is found) then
find and display the record based on the CompanyID
Else
enter new record (CompanyId) is autonumber
[BusinessStreet] = (whatever I just typed that was not found)
me.Company.setfocus
end if

Any help would be appreciated.

 

[Dan Artuso]

Hi,
Into what table do you want to enter the new record?
What is the row source of your combo?

I would think what you want to do is possible, but we need to know
your form's record source and your combo's row source.

The NotInList event is usually used to make a new entry into the table that
feeds the combo.

 

[Chaster]

The row source is the contacts table.
I guess I am not explaining this very well.
What I am trying to do is not have to re-enter the address information that
was not found in the combo

So if the data I type into the combobox is not in the underlying table how
would I programmatically add a new record, insert the data that is displayed
in the combo box into that new records [BusinessStreet] field.

I think what I am trying to say is

If (the value I just typed into the combo box is not in the underlying
table)then
add a new record
[BusinessStreet] = (what I just typed in the combo box that was not found in
the underlying table)
me.company.setfocus
else
find and display the record that matches the value that was typed into the
combobox
end if

Okay, we're getting closer.
What is the table that your combo is getting it's data from?
In order for your 'new entry that you type in' to appear in the
combo, it has to be added to it's underlying table.
Is it getting that data from your Contacts table?
Look in the row source property for the combo.

--
HTH
Dan Artuso, Access MVP

Thank you for your response.

My table name is Contacts.
My forms control source is based on this table.

I inserted my combobox using the wizard that looks up records in my table
and then finds the record based on the selection from the combobox. The two
fields in the combo are [CompanyId].Column(0)(hidden) and
[BusinessStreet].Column(1)
I changed the sort order to [BusinessStreet] ascending.

I do a drop down marco in the gotfocus of the Combo and the combo displays
12 rows.

when we start typing in the address we can see that
1. The address is found
2. The address is found several times
3. The address is not in our table.

Depending on what we see in the data we would want to do one of 3 things.
A: Push ESC and start searching for another record.
B: Hit the enter key and be taken to the appropriate record to edit the
data.
(if the find data failed then)
C: add a new record that has the data that we just typed into the combobox
inserted into the new records [BusinessStreet] field and set the focus to
the new records [Company] Field

Hope this explains this.

TIA

Hi,
Into what table do you want to enter the new record?
What is the row source of your combo?

I would think what you want to do is possible, but we need to know
your form's record source and your combo's row source.

The NotInList event is usually used to make a new entry into the table that
feeds the combo.

--
HTH
Dan Artuso, Access MVP


With a combobox I want to search for a companies address and if

found go
to

that record by using the [CompanyId] field.

I have that working ok.

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CompanyID] = " & Str(Me![Combo35])
Me.Bookmark = rs.Bookmark
Me.Rest.SetFocus

If the data is not found I want to enter a new record and insert the data
that was just typed into the combox into the [BusinessStreet] field

in
the

new record and set the focus to another control.

I have tried this on the after update of the not in list property

but it
is

not working.

Basically it would be something like

If (the data I just typed is found) then
find and display the record based on the CompanyID
Else
enter new record (CompanyId) is autonumber
[BusinessStreet] = (whatever I just typed that was not found)
me.Company.setfocus
end if

Any help would be appreciated.

 

[Dan Artuso]

Hi,
You're explaining it okay, I just needed to know certain things
to write the code.
So, let's try this:

Private Sub Combo29_NotInList(NewData As String, Response As Integer)
Dim strSql As String

strSql = "Insert Into Contacts (BusinessStreet) Values('" & _
NewData & "')"
CurrentDb.Execute strSql, dbFailOnError
Response = acErrDataAdded
DoCmd.GoToRecord , , acLast
Me.BusinessStreet = NewData
Me.Company.SetFocus
Response = acErrDataAdded
End Sub

I haven't tested the above but see if it does what you want.

--
HTH
Dan Artuso, Access MVP

The row source is the contacts table.
I guess I am not explaining this very well.
What I am trying to do is not have to re-enter the address information that
was not found in the combo

So if the data I type into the combobox is not in the underlying table how
would I programmatically add a new record, insert the data that is displayed
in the combo box into that new records [BusinessStreet] field.

I think what I am trying to say is

If (the value I just typed into the combo box is not in the underlying
table)then
add a new record
[BusinessStreet] = (what I just typed in the combo box that was not found in
the underlying table)
me.company.setfocus
else
find and display the record that matches the value that was typed into the
combobox
end if

Okay, we're getting closer.
What is the table that your combo is getting it's data from?
In order for your 'new entry that you type in' to appear in the
combo, it has to be added to it's underlying table.
Is it getting that data from your Contacts table?
Look in the row source property for the combo.

--
HTH
Dan Artuso, Access MVP

Thank you for your response.

My table name is Contacts.
My forms control source is based on this table.

I inserted my combobox using the wizard that looks up records in my table
and then finds the record based on the selection from the combobox. The two
fields in the combo are [CompanyId].Column(0)(hidden) and
[BusinessStreet].Column(1)
I changed the sort order to [BusinessStreet] ascending.

I do a drop down marco in the gotfocus of the Combo and the combo displays
12 rows.

when we start typing in the address we can see that
1. The address is found
2. The address is found several times
3. The address is not in our table.

Depending on what we see in the data we would want to do one of 3 things.
A: Push ESC and start searching for another record.
B: Hit the enter key and be taken to the appropriate record to edit the
data.
(if the find data failed then)
C: add a new record that has the data that we just typed into the combobox
inserted into the new records [BusinessStreet] field and set the focus to
the new records [Company] Field

Hope this explains this.

TIA



Hi,
Into what table do you want to enter the new record?
What is the row source of your combo?

I would think what you want to do is possible, but we need to know
your form's record source and your combo's row source.

The NotInList event is usually used to make a new entry into the table
that
feeds the combo.

--
HTH
Dan Artuso, Access MVP


With a combobox I want to search for a companies address and if found go
to
that record by using the [CompanyId] field.

I have that working ok.

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CompanyID] = " & Str(Me![Combo35])
Me.Bookmark = rs.Bookmark
Me.Rest.SetFocus

If the data is not found I want to enter a new record and insert the
data
that was just typed into the combox into the [BusinessStreet] field in
the
new record and set the focus to another control.

I have tried this on the after update of the not in list property but it
is
not working.

Basically it would be something like

If (the data I just typed is found) then
find and display the record based on the CompanyID
Else
enter new record (CompanyId) is autonumber
[BusinessStreet] = (whatever I just typed that was not found)
me.Company.setfocus
end if

Any help would be appreciated.

 

[Chaster]

Thanks for hanging in there with me.

Were close but not cigar
1st

I get a can't go to specified record error at GoToRecord, , acLast

2nd

Really strange! The code does insert a new record, with the proper data,
but it inserts it 262 times.
I have tried a couple of tests and this is the result each time

I have 5625 records in my table
I put in 123456 Test Street into combobox and hit enter. The system seems
to hang for a few seconds before I get the error above.

When I go to my underlying table Contacts I have 5887 records 262 with
123456 Test Street in the [BusinessStreet] field.

I deleted those 262 records and tested
888 Zone Street

Same thing as above 5887 records 262 with 888 Zone Street in
[BusinessStreet] field.


?

Hi,
You're explaining it okay, I just needed to know certain things
to write the code.
So, let's try this:

Private Sub Combo29_NotInList(NewData As String, Response As Integer)
Dim strSql As String

strSql = "Insert Into Contacts (BusinessStreet) Values('" & _
NewData & "')"
CurrentDb.Execute strSql, dbFailOnError
Response = acErrDataAdded
DoCmd.GoToRecord , , acLast
Me.BusinessStreet = NewData
Me.Company.SetFocus
Response = acErrDataAdded
End Sub

I haven't tested the above but see if it does what you want.

--
HTH
Dan Artuso, Access MVP

The row source is the contacts table.
I guess I am not explaining this very well.
What I am trying to do is not have to re-enter the address information that
was not found in the combo

So if the data I type into the combobox is not in the underlying table how
would I programmatically add a new record, insert the data that is displayed
in the combo box into that new records [BusinessStreet] field.

I think what I am trying to say is

If (the value I just typed into the combo box is not in the underlying
table)then
add a new record
[BusinessStreet] = (what I just typed in the combo box that was not found in
the underlying table)
me.company.setfocus
else
find and display the record that matches the value that was typed into the
combobox
end if

Okay, we're getting closer.
What is the table that your combo is getting it's data from?
In order for your 'new entry that you type in' to appear in the
combo, it has to be added to it's underlying table.
Is it getting that data from your Contacts table?
Look in the row source property for the combo.

--
HTH
Dan Artuso, Access MVP


Thank you for your response.

My table name is Contacts.
My forms control source is based on this table.

I inserted my combobox using the wizard that looks up records in my table
and then finds the record based on the selection from the combobox.

The
two

fields in the combo are [CompanyId].Column(0)(hidden) and
[BusinessStreet].Column(1)
I changed the sort order to [BusinessStreet] ascending.

I do a drop down marco in the gotfocus of the Combo and the combo displays
12 rows.

when we start typing in the address we can see that
1. The address is found
2. The address is found several times
3. The address is not in our table.

Depending on what we see in the data we would want to do one of 3 things.
A: Push ESC and start searching for another record.
B: Hit the enter key and be taken to the appropriate record to edit the
data.
(if the find data failed then)
C: add a new record that has the data that we just typed into the combobox
inserted into the new records [BusinessStreet] field and set the

focus
to

the new records [Company] Field

Hope this explains this.

TIA



Hi,
Into what table do you want to enter the new record?
What is the row source of your combo?

I would think what you want to do is possible, but we need to know
your form's record source and your combo's row source.

The NotInList event is usually used to make a new entry into the table
that
feeds the combo.

--
HTH
Dan Artuso, Access MVP


With a combobox I want to search for a companies address and if found go
to
that record by using the [CompanyId] field.

I have that working ok.

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CompanyID] = " & Str(Me![Combo35])
Me.Bookmark = rs.Bookmark
Me.Rest.SetFocus

If the data is not found I want to enter a new record and insert the
data
that was just typed into the combox into the [BusinessStreet]

field
in

the
new record and set the focus to another control.

I have tried this on the after update of the not in list

property
but it

is
not working.

Basically it would be something like

If (the data I just typed is found) then
find and display the record based on the CompanyID
Else
enter new record (CompanyId) is autonumber
[BusinessStreet] = (whatever I just typed that was not found)
me.Company.setfocus
end if

Any help would be appreciated.