ACCESS FEED: RE: [MS_AccessPros] ComboBox/Continuous Form question

Hi John,

That code was working so I didn't want to change it just yet. The code also has the status (gate) change in it also.

The tblNonProdQuoteReques table is the Record Source for the form and fields EXCEPT for the combo box with is a look up table. Which is:
My rowsource = SELECT tblGate.Gate, tblGate.GateStatus FROM tblGate;

If the ControlSource bound to Gate (Status change) the query doesn't work.

It seems like you can't do both at the same time. Is this a limitation of trying to use a combo box with continuous forms?

Thank you.

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Monday, September 26, 2011 10:49 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] ComboBox/Continuous Form question

Sally-

That's using a sledgehammer where an ice pick would do!

I assume tblNonProdQuoteRequest is in the Record Source of the form and fields
Gate1, Gate2, and Gate are also in the Record Source.

Private Sub cmbGateChange_AfterUpdate()

Select Case Me.cmbGateChange
Case 1
Me.Gate1 = Now()
Case 2
Me.Gate2 = Now()
End Select

End Sub

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals%40yahoogroups.com>] On Behalf Of Sally Vega
Sent: Monday, September 26, 2011 7:34 PM
To: MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] ComboBox/Continuous Form question

Hi John,

This is what I have in the afterupdate event for the combo box. Gate is field
where the status from the combobox is going and Gate1, Gate2, etc is where the
timestamp goes. They're both in the same table. The ComboBox is just a lookup
table.

Dim SQL1 As String
Dim SQL2 As String

SQL1 = "UPDATE tblNonProdQuoteRequest" _
& " SET tblNonProdQuoteRequest.Gate1 = Now()," _
& " tblNonProdQuoteRequest.Gate = 1" _
& " WHERE [cmbGateChange] = 1" _
& " And [PartNo]=" & "'" & Me![txtPartNo] & "'"

SQL2 = "UPDATE tblNonProdQuoteRequest" _
& " SET tblNonProdQuoteRequest.Gate2 = Now()," _
& " tblNonProdQuoteRequest.Gate = 2" _
& " WHERE [cmbGateChange] = 2" _
& " And [PartNo]=" & "'" & Me![txtPartNo] & "'"

DoCmd.RunSQL SQL1
DoCmd.RunSQL SQL2

Me.cmbGateChange.Value = Null

Thanks.

From: MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals%40yahoogroups.com>] On Behalf Of John Viescas
Sent: Monday, September 26, 2011 10:23 AM
To: MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] ComboBox/Continuous Form question

Sally-

You should still be able to run your code in the AfterUpdate event of the combo.
What is the code you have now?

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From:
MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals%40yahoogroups.com><mailto:MS_Access_Professionals%40yahoogr
oups.com>
[mailto:MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals%40yahoogroups.com><mailto:MS_Access_Professionals%4
0yahoogroups.com>] On Behalf Of Sally Vega
Sent: Monday, September 26, 2011 7:12 PM
To:
MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals%40yahoogroups.com><mailto:MS_Access_Professionals%40yahoogr
oups.com>
Subject: RE: [MS_AccessPros] ComboBox/Continuous Form question

Hi John,

The Me.cmbStatusChang = Null worked but I still have the problem of all of the
statuses changing to the same thing for all rows in the continuous form.

In the AfterUpdate event is where I'm doing an SQL UPDATE depending on the
status chosen from the combo box and a timestamp in the table.

If I bind the combo box then the update doesn't work. If I bound the combo box
would I need to have the user press a button to update the status then?

Thanks.

From:
MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals%40yahoogroups.com><mailto:MS_Access_Professionals%40yahoogr
oups.com>
[mailto:MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals%40yahoogroups.com><mailto:MS_Access_Professionals%4
0yahoogroups.com>] On Behalf Of John Viescas
Sent: Monday, September 26, 2011 9:56 AM
To:
MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals%40yahoogroups.com><mailto:MS_Access_Professionals%40yahoogr
oups.com>
Subject: RE: [MS_AccessPros] ComboBox/Continuous Form question

Sally-

What happens in the AfterUpdate event of the combo box? I'm having a hard time
seeing why the combo isn't bound.

But to answer your question, do:

Me.cmbStatusChange = Null

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From:
MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals%40yahoogroups.com><mailto:MS_Access_Professionals%40yahoogr
oups.com><mailto:MS_Access_Professionals%40yahoogr
oups.com>
[mailto:MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals%40yahoogroups.com><mailto:MS_Access_Professionals%4
0yahoogroups.com><mailto:MS_Access_Professionals%4
0yahoogroups.com>] On Behalf Of Sally Vega
Sent: Monday, September 26, 2011 6:36 PM
To:
MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals%40yahoogroups.com><mailto:MS_Access_Professionals%40yahoogr
oups.com><mailto:MS_Access_Professionals%40yahoogr
oups.com>
Subject: RE: [MS_AccessPros] ComboBox/Continuous Form question

Hi John,

The combo box is for the user to switch from one status to another. I want the
user to be able to change the status of each row individually. I tried
Me.cmbStatusChange = "False" and this resets all of the combo boxes to 0, how
can I have them reset themselves to null like when I first open the form?

Thank you.

From:
MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals%40yahoogroups.com><mailto:MS_Access_Professionals%40yahoogr
oups.com><mailto:MS_Access_Professionals%40yahoogr
oups.com>
[mailto:MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals%40yahoogroups.com><mailto:MS_Access_Professionals%4
0yahoogroups.com><mailto:MS_Access_Professionals%4
0yahoogroups.com>] On Behalf Of John Viescas
Sent: Monday, September 26, 2011 9:20 AM
To:
MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals%40yahoogroups.com><mailto:MS_Access_Professionals%40yahoogr
oups.com><mailto:MS_Access_Professionals%40yahoogr
oups.com>
Subject: RE: [MS_AccessPros] ComboBox/Continuous Form question

Sally-

Have you considered putting the combo box in the form header?

If the combo box were bound, I could suggest a solution using an overlay text
box. Also, do you need the combo only when on a new row? Consider
showing/hiding the combo box in the Current event of the form based on the
setting of Me.NewRecord.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From:
MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals%40yahoogroups.com><mailto:MS_Access_Professionals%40yahoogr
oups.com><mailto:MS_Access_Professionals%40yahoogr
oups.com><mailto:MS_Access_Professionals%40yahoogr
oups.com>
[mailto:MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals%40yahoogroups.com><mailto:MS_Access_Professionals%4
0yahoogroups.com><mailto:MS_Access_Professionals%4
0yahoogroups.com><mailto:MS_Access_Professionals%4
0yahoogroups.com>] On Behalf Of Sally Vega
Sent: Monday, September 26, 2011 6:05 PM
To:
MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals%40yahoogroups.com><mailto:MS_Access_Professionals%40yahoogr
oups.com><mailto:MS_Access_Professionals%40yahoogr
oups.com><mailto:MS_Access_Professionals%40yahoogr
oups.com>
Subject: [MS_AccessPros] ComboBox/Continuous Form question

Good morning all,

I'm not new to programming but I'm new to Access/VBA. I'm trying to do
something that should have been easy but can't find a way to do it.

I have a combo box in my continuous form and it is not bound. What the combo
box is doing is an update of the status (from 1 to 7) and then a timestamp of
when the status changed. This part is working.

The problem is that when I change the combo box for one row, it changes all of
the them to the same thing. How can I have it change only to the value in that
row? It needs to remain unbound so that I can do the update.

I would also be happy with just having all of the combo boxes reset themselves
to nothing once the update is done.

Thank you.

This email (and all attachments) is for the sole use of the intended
recipient(s) and may contain privileged and/or proprietary information. Any
unauthorized review, use, disclosure or distribution is prohibited. If you are
not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message.

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

This email (and all attachments) is for the sole use of the intended
recipient(s) and may contain privileged and/or proprietary information. Any
unauthorized review, use, disclosure or distribution is prohibited. If you are
not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message.

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

This email (and all attachments) is for the sole use of the intended
recipient(s) and may contain privileged and/or proprietary information. Any
unauthorized review, use, disclosure or distribution is prohibited. If you are
not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message.

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

This email (and all attachments) is for the sole use of the intended
recipient(s) and may contain privileged and/or proprietary information. Any
unauthorized review, use, disclosure or distribution is prohibited. If you are
not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message.

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

[Non-text portions of this message have been removed]