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.Column(2)
[Location] = Recipient.Column(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.Column(2)
[Location] = Recipient.Column(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_NotInList(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!SalesInitials.Form!SalesInitials = NewData
End If
Response = acDataErrContinue
Me!SalesInitID.Undo
DoCmd.CancelEvent
End Sub

[DTecMeister]

On Aug 28, 3:55 pm, "injanib via AccessMonster.com" <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.Column(2)
[Location] = Recipient.Column(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.Column(2)
[Location] = Recipient.Column(3)
end if.

--
Message posted viahttp://www.accessmonster.com

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

Private Sub Recipient_NotInList(NewData As String, Response As
Integer)
Me.Floor.Value = "1"
Me.Location.Value = "?"
Response = acDataErrContinue
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.com" <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.accessmonster.com

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

Private Sub Recipient_NotInList(NewData As String, Response As
Integer)
Me.Floor.Value = "1"
Me.Location.Value = "?"
Response = acDataErrContinue
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.com
http://www.accessmonster.com/Uwe/For...ccess/200708/1

[DTecMeister]

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

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

DTecMeisterwrote:

On Aug 28, 3:55 pm, "injanib via AccessMonster.com" <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.accessmonster.com

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

Private Sub Recipient_NotInList(NewData As String, Response As
Integer)
Me.Floor.Value = "1"
Me.Location.Value = "?"
Response = acDataErrContinue
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.comhttp://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/2007...- Hide quoted text -

- Show quoted text -

I got it. You'll need to programmatically add the new row to the
table containing the rowsource information and set the fields with
default values:
Dim MyConnection As New ADODB.Connection
Dim AddSQL As New ADODB.Command
MyConnection.Open "DSN=Set_Me_to_your_ODBCCONNECTION"
MyConnection.CursorLocation = adUseClient
Set AddSQL.ActiveConnection = MyConnection
AddSQL.CommandText = "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.