Combo box after update event

[injanib via AccessMonster.com]

I have a combo box called "Recipient" who's row source is a table called
"Main" with three columns. The three columns are "Name", "Floor", "Location".
Following the combo box are two fields called "Floor" and "Location". The
after update event of the combo box runs the following lines to update the
next two fields.

[Floor] = Recipient.Colum n(2)
[Location] = Recipient.Colum n(3)

The combo box is not limited to the values in its rowsource table. What I
want is that if the value entered in the combo box is not in its rowsource it
should update the next two fields as follows.

[Floor] = "1"
[Location] = "?"

I tried the following code and it gives me the error "You canceled the
previous operation".

if DCount("*", "Main", "[Name]=" & me.Recipient)< 1 then
[Floor]="1"
[Location]="?"
else
[Floor] = Recipient.Colum n(2)
[Location] = Recipient.Colum n(3)
end if.

--
Message posted via http://www.accessmonster.com

[ARC]

Do you have an add/edit form that allows changes to the table that stores
Name/Floor/Location? If so, you could just do a not in list event that opens
the add/edit form to a new record. Something like:

Private Sub SalesInitID_Not InList(NewData As String, Response As Integer)
If MsgBox("The Sales Person Initials you entered could not be found. Would
you like to add it now?", vbYesNo + vbInformation, "Item Not Found - Add?")
= vbYes Then
DoCmd.OpenForm "SalesInitials" , , , , acFormAdd
Forms!SalesInit ials.Form!Sales Initials = NewData
End If
Response = acDataErrContin ue
Me!SalesInitID. Undo
DoCmd.CancelEve nt
End Sub

[DTecMeister]

On Aug 28, 3:55 pm, "injanib via AccessMonster.c om" <u35551@uwe>
wrote:

I have a combo box called "Recipient" who's row source is a table called
"Main" with three columns. The three columns are "Name", "Floor", "Location".
Following the combo box are two fields called "Floor" and "Location". The
after update event of the combo box runs the following lines to update the
next two fields.

[Floor] = Recipient.Colum n(2)
[Location] = Recipient.Colum n(3)

The combo box is not limited to the values in its rowsource table. What I
want is that if the value entered in the combo box is not in its rowsource it
should update the next two fields as follows.

[Floor] = "1"
[Location] = "?"

I tried the following code and it gives me the error "You canceled the
previous operation".

if DCount("*", "Main", "[Name]=" & me.Recipient)< 1 then
[Floor]="1"
[Location]="?"
else
[Floor] = Recipient.Colum n(2)
[Location] = Recipient.Colum n(3)
end if.

--
Message posted viahttp://www.accessmonst er.com

Insure the "Limit to list" property for Recipient is True then:

Private Sub Recipient_NotIn List(NewData As String, Response As
Integer)
Me.Floor.Value = "1"
Me.Location.Val ue = "?"
Response = acDataErrContin ue
End Sub

You'll have to add some if you then want the updated fields to be
added to the drop-down list or add them to the table.

[injanib via AccessMonster.com]

this works, but I can not make the focus to leave Recipient field. Not even
by clicking else where.

DTecMeister wrote:

>On Aug 28, 3:55 pm, "injanib via AccessMonster.c om" <u35551@uwe>
wrote:

>I have a combo box called "Recipient" who's row source is a table called
"Main" with three columns. The three columns are "Name", "Floor", "Location".

[quoted text clipped - 25 lines]

>--
Message posted viahttp://www.accessmonst er.com

Insure the "Limit to list" property for Recipient is True then:

Private Sub Recipient_NotIn List(NewData As String, Response As
Integer)
Me.Floor.Val ue = "1"
Me.Location.Va lue = "?"
Response = acDataErrContin ue
End Sub

You'll have to add some if you then want the updated fields to be
added to the drop-down list or add them to the table.

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200708/1

[DTecMeister]

On Aug 29, 9:38 am, "injanib via AccessMonster.c om" <u35551@uwe>
wrote:

this works, but I can not make the focus to leave Recipient field. Not even
by clicking else where.

DTecMeisterwrot e:

On Aug 28, 3:55 pm, "injanib via AccessMonster.c om" <u35551@uwe>
wrote:

I have a combo box called "Recipient" who's row source is a table called
"Main" with three columns. The three columns are "Name", "Floor", "Location".

[quoted text clipped - 25 lines]

--
Message posted viahttp://www.accessmonst er.com

Insure the "Limit to list" property for Recipient is True then:

Private Sub Recipient_NotIn List(NewData As String, Response As
Integer)
Me.Floor.Value = "1"
Me.Location.Val ue = "?"
Response = acDataErrContin ue
End Sub

You'll have to add some if you then want the updated fields to be
added to the drop-down list or add them to the table.

--
Message posted via AccessMonster.c omhttp://www.accessmonst er.com/Uwe/Forums.aspx/databases-ms-access/2007...- Hide quoted text -

- Show quoted text -

I got it. You'll need to programmaticall y add the new row to the
table containing the rowsource information and set the fields with
default values:
Dim MyConnection As New ADODB.Connectio n
Dim AddSQL As New ADODB.Command
MyConnection.Op en "DSN=Set_Me_to_ your_ODBCCONNEC TION"
MyConnection.Cu rsorLocation = adUseClient
Set AddSQL.ActiveCo nnection = MyConnection
AddSQL.CommandT ext = "insert into Recipients values ('" + NewData +
"',''1,'?') "
AddSQL.Execute
Response = acDataErrAdded

I use linked tables so you may be able to do this without the odbc
connection, but you get the idea.