Jumat, 30 November 2012

[MS_AccessPros] Referencing control on subform to requery

 

The db that I am developing (Access 2010 on Windows 7) consist of a main form with a tabcontrol. Each tab consist of a split form. Within the split form are combo-boxes with data based on a saved query. The issue is requerying the row source for the various combo-boxes when the record on the main form changes. Each of the tabs contain different data from the db tables. Each tab only uses one table. Each tab is linked to the main form thru an id field.
Query definition for the combo-box row source:
SELECT tblParcel.lngParcelID, tblParcel.lngParcelNum
FROM tblParcel
WHERE (((tblParcel.lngTPTID)=GETTPTID()));

The function GETTPTID() retrieves the ID which is updated every time the main form updates to a new record.
I am trying to use the TabCtl On Change to initiate the requery for the combo-box. Tried several approaches at a loss. The combo-box is used to list the currently identified property parcels used when sending correspondence. Not sure if I have explained the issue well enough? Any suggestions would be greatly appreciated.

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)
Recent Activity:
.

__,_._,___

[MS_AccessPros] Send Form Filter results to query

 

Hello all,

Is there a way to send the results of a filter on a form as a query result or make table?

I would like to run a command button that filters a form for specific results and then send that to a query or table to see the results in a datasheet view.

I have the filter part as shown below but would like to see it in a datasheet view.

DoCmd.ApplyFilter , "[StatusType] = 'In Process'"

Thank You

Jim Wagner

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)
Recent Activity:
.

__,_._,___

RE: [MS_AccessPros] Timer

John,

Thanks and WOW!. I will try to work through this code this weekend. I will
let you know how it goes.



Bill Singer



From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Friday, November 30, 2012 11:20 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Timer





Bill-

OK. Let's assume the text box is called txtClock. Format="hh:mm:nn"

Three command buttons: cmdStart (Caption START), cmdPause (Caption PAUSE,
Enabled = No), cmdStop (Caption STOP, enabled = No)

Private Sub cmdStart_Click()
Dim datTimeValue As Date
' Make sure it's a valid date/time value
If Not IsDate(Me.txtClock) Then
MsgBox "You must enter a valid time value."
Exit Sub
End If
' Grab the value as a Time - strip off any date part
datTimeValue = TimeValue(Me.txtClock)
' Put it back as hh:mm:ss
Me.txtClock = datTimeValue
' Make sure it's not zero
If Format(datTimeValue, "hh:mm:nn") = "00:00:00" Then
MsgBox "You must set the starting time to something other than
zero."
Exit Sub
End If
' Turn on the timer interval
Me.TimerInterval = 1000
' Enable the other two buttons
Me.cmdPause.Enabled = True
Me.cmdStop.Enabled = True
End Sub

Private Sub Form_Timer()
Dim datTimeValue As Date
' Grab the current value
datTimeValue = TimeValue(Me.txtClock)
' Subtract 1 second
datTimeValue = datTimeValue - #00:00:01#
' Update the visual
Me.txtClock = datTimeValue
' If decremented to 0,
If Format(datTimeValue, "hh:mm:nn") = "00:00:00" Then
' Turn off the timer
Me.TimerInterval = 0
' Make a sound
Beep
' Put the focus on the text box
Me.txtClock.SetFocus
' Disable the Pause and Stop buttons
Me.cmdPause.Enabled = False
Me.cmdStop.Enabled = False
End If
End Sub

Private Sub cmdPause_Click()
' Stop the timer
Me.TimerInterval = 0
End Sub

Private Sub cmdStop_Click()
' Stop the timer
Me.TimerInterval = 0
' Clear the text box
Me.txtClock = #00:00:00#
' Put the focus there
Me.txtClock.SetFocus
' Disable pause and stop
Me.cmdPause.Enabled = False
Me.cmdStop.Enabled = False
End Sub

That should get you started©

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

-----Original Message-----
From: Bill Singer <Bill.Singer@at-group.net
<mailto:Bill.Singer%40at-group.net> >
Reply-To: <MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> >
Date: Friday, November 30, 2012 5:33 PM
To: <MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> >
Subject: RE: [MS_AccessPros] Timer

John,

Well I guess that is bad news and good news. I was hoping for some type of
"Clock" function. The good news is that it can still be done. Your
question at the end of your email made me laugh. Absolutely I will need
help writing that code! Anything you can send my way would be great.

Thanks,

Bill Singer

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: Friday, November 30, 2012 9:59 AM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: Re: [MS_AccessPros] Timer

Bill-

If you just want a digital countdown, you can build your own with a Text
Box and the Timer event. Enter the total time you want to start at in the
text box, then click a Start button. Code behind the Start button would
then set the form's TimerInterval property to 1000 (1000 milliseconds = 1
second) to start the clock. In your On Timer event procedure, subtract 1
second from the clock value. You could also provide a Pause button (set
TimerInterval to 0 to stop it from firing) and a Stop button (set
TimerInterval to 0 and set the Text Box to 0). Need some help writing the
code?

The other alternative is to search for a clock or countdown ActiveX
control on the web.

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

-----Original Message-----
From: Bill Singer <Bill.Singer@at-group.net
<mailto:Bill.Singer%40at-group.net>
<mailto:Bill.Singer%40at-group.net> >
Reply-To: <MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com> >
Date: Friday, November 30, 2012 4:32 PM
To: <MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com> >
Subject: [MS_AccessPros] Timer

I am wondering if access has a clock function or something that would work
like a game clock in a football game. I want to set the time, click the
start button and then have the clock start counting down.

Thanks,

Bill

MN

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

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

Yahoo! Groups Links

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

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

Yahoo! Groups Links





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



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

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Re: [MS_AccessPros] write a program using a subquery entirely from Design View?

 

Don't know why the > symbol disappeared. Try this:

>(SELECT Max(Price) FROM MyTable As T2 WHERE T2.Title <> MyTable.Title)

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

-----Original Message-----
From: John Viescas <JohnV@msn.com>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Friday, November 30, 2012 6:42 PM
To: <MS_Access_Professionals@yahoogroups.com>
Subject: Re: [MS_AccessPros] write a program using a subquery entirely
from Design View?

Dear Math Doctor- (name?)

Your problem is nonsensical because there can be no book in the table
whose price is greater than ALL books. Do you mean greater than all OTHER
books?

You can attempt to solve this by typing the SQL in the Criteria box.
Start a new query on your table. Drag the Title and Price fields to the
query grid. Clear the Show box under Price unless you also want to show
this. Now put this in the Criteria box under Price:

>(SELECT Max(Price) FROM MyTable As T2 WHERE T2.Title <> MyTable.Title)

Note that "MyTable" is the name of your table.

This will return no rows, however, if there is more than one book in the
table that has the same highest price. Problem left for you to solve.

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

-----Original Message-----
From: The Math Doctor <themathdoctor@yahoo.com>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Friday, November 30, 2012 6:30 PM
To: <MS_Access_Professionals@yahoogroups.com>
Subject: [MS_AccessPros] write a program using a subquery entirely from
Design View?

I would like to show my students how to solve the following program in
Access' design mode without actually tweaking the SQL code, if possible.

A table stores the names of some books and their prices. Print the names
of all books whose price is greater than that of all the books in the
table.

Thanks.

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

Yahoo! Groups Links

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

Yahoo! Groups Links

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (3)
Recent Activity:
.

__,_._,___

Re: [MS_AccessPros] write a program using a subquery entirely from Design View?

 

Dear Math Doctor- (name?)

Your problem is nonsensical because there can be no book in the table
whose price is greater than ALL books. Do you mean greater than all OTHER
books?

You can attempt to solve this by typing the SQL in the Criteria box.
Start a new query on your table. Drag the Title and Price fields to the
query grid. Clear the Show box under Price unless you also want to show
this. Now put this in the Criteria box under Price:

>(SELECT Max(Price) FROM MyTable As T2 WHERE T2.Title <> MyTable.Title)

Note that "MyTable" is the name of your table.

This will return no rows, however, if there is more than one book in the
table that has the same highest price. Problem left for you to solve.

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

-----Original Message-----
From: The Math Doctor <themathdoctor@yahoo.com>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Friday, November 30, 2012 6:30 PM
To: <MS_Access_Professionals@yahoogroups.com>
Subject: [MS_AccessPros] write a program using a subquery entirely from
Design View?

I would like to show my students how to solve the following program in
Access' design mode without actually tweaking the SQL code, if possible.

A table stores the names of some books and their prices. Print the names
of all books whose price is greater than that of all the books in the
table.

Thanks.

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

Yahoo! Groups Links

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (2)
Recent Activity:
.

__,_._,___

[MS_AccessPros] Re: Using UPDATE to change data in a table via combobox and command button

 

What a silly mistake! Thanks a lot John!

Tony

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Tony-
>
> Ah, well, you have to EXECUTE the query:
>
> Private Sub commandVoid_Click()
> Dim strSQL As String
>
> If MsgBox("Are you sure you want to void this ticket?", vbYesNo) =
> vbYes Then
> strSQL = "UPDATE tableWarehouseData " & _
> "SET Status = '" & V & "' " & _
> "WHERE Ticket = " & Me.comboVoidTicket
> CurrentDB.Execute strSQL, dbFailOnError
> End If
> End Sub
>
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
>
>
> -----Original Message-----
> From: Tony <GKillah36@...>
> Reply-To: <MS_Access_Professionals@yahoogroups.com>
> Date: Friday, November 30, 2012 5:38 PM
> To: <MS_Access_Professionals@yahoogroups.com>
> Subject: [MS_AccessPros] Re: Using UPDATE to change data in a table via
> combobox and command button
>
> Ahh, okay. Thanks John. I don't get the error anymore, but it doesn't
> actually change the value if the status field to A for those tickets.
> Very weird.
>
> And ticket is a numeric field FYI.
>
> Here is what the code looks like now:
>
> Private Sub commandVoid_Click()
>
> Dim strSQL As String
>
> If MsgBox("Are you sure you want to void this ticket?", vbYesNo) =
> vbYes Then
> strSQL = "UPDATE tableWarehouseData " & _
> "SET Status = '" & V & "' " & _
> "WHERE Ticket = " & Me.comboVoidTicket
> End If
> End Sub
>
>
>
>
> --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@>
> wrote:
> >
> > Tony-
> >
> > Your quotes are all messed up. Try this:
> >
> > strSQL = "UPDATE tableWarehouseData " & _
> > "SET Status = '" & V & "' " & _
> > "WHERE ticket = '" & Me.comboVoidTicket & "'"
> >
> >
> > That assumes that ticket is a text field. If not, then do:
> >
> > strSQL = "UPDATE tableWarehouseData " & _
> > "SET Status = '" & V & "' " & _
> > "WHERE ticket = " & Me.comboVoidTicket
> >
> > John Viescas, Author
> > Microsoft Access 2010 Inside Out
> > Microsoft Access 2007 Inside Out
> > Microsoft Access 2003 Inside Out
> > Building Microsoft Access Applications
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Paris, France)
> >
> >
> >
> >
> >
> >
> > -----Original Message-----
> > From: Gkillah36 <GKillah36@>
> > Reply-To: <MS_Access_Professionals@yahoogroups.com>
> > Date: Friday, November 30, 2012 6:17 AM
> > To: <MS_Access_Professionals@yahoogroups.com>
> > Subject: Re: [MS_AccessPros] Using UPDATE to change data in a table via
> > combobox and command button
> >
> > V just comes from me. I just want to have all of the Status field values
> > change to V for the ticket that the user selects. The Status field is
> > text.
> >
> > Thanks,
> > Tony
> >
> >
> >
> >
> >
> >
> >
> > -----Original Message-----
> > From: Duane Hookom <duanehookom@>
> > To: Access Professionals Yahoo Group
> > <ms_access_professionals@yahoogroups.com>
> > Sent: Fri, Nov 30, 2012 12:15 am
> > Subject: RE: [MS_AccessPros] Using UPDATE to change data in a table via
> > combobox and command button
> >
> >
> >
> >
> >
> > Where does V come from? Is the Status field text or numeric?
> >
> > Learn how to implement
> > Debug.Print strSQL
> > So you can troubleshoot on your own.
> >
> > Duane Hookom MVP
> > MS Access
> >
> > > From: GKillah36@
> > >
> > > Hi all,
> > >
> > > Please see my VB below:
> > >
> > > Private Sub commandVoid_Click()
> > >
> > > Dim strSQL As String
> > >
> > > If MsgBox("Are you sure you want to void this ticket?", vbYesNo) =
> >vbYes
> > >Then
> > > strSQL = "UPDATE tableWarehouseData " & _
> > > "SET Status = " & V & " " & _
> > > "WHERE ticket = " & Me.comboVoidTicket & ""
> > > CurrentDb.Execute strSQL, dbFailOnError
> > > End If
> > > End Sub
> > >
> > >
> > > ----------------------------------------
> > >
> > > This should change the Status field of all tickets in the table
> > >tableWarehouseData to V when clicked based on the selected ticket number
> > >in the comboVoidTicket combobox. However, I'm getting an error of
> > >"Run-time error '3144': Syntax error in UPDATE statement." What am I
> > >missing here?
> > >
> > > Thank you,
> > > Tony
> > >
> > > FYI, I'm using Access 2010.
> >
> >
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (7)
Recent Activity:
.

__,_._,___

[MS_AccessPros] write a program using a subquery entirely from Design View?

 

I would like to show my students how to solve the following program in Access' design mode without actually tweaking the SQL code, if possible.

A table stores the names of some books and their prices. Print the names of all books whose price is greater than that of all the books in the table.

Thanks.

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)
Recent Activity:
.

__,_._,___

Re: [MS_AccessPros] Timer

 

Bill-

OK. Let's assume the text box is called txtClock. Format="hh:mm:nn"

Three command buttons: cmdStart (Caption START), cmdPause (Caption PAUSE,
Enabled = No), cmdStop (Caption STOP, enabled = No)

Private Sub cmdStart_Click()
Dim datTimeValue As Date
' Make sure it's a valid date/time value
If Not IsDate(Me.txtClock) Then
MsgBox "You must enter a valid time value."
Exit Sub
End If
' Grab the value as a Time - strip off any date part
datTimeValue = TimeValue(Me.txtClock)
' Put it back as hh:mm:ss
Me.txtClock = datTimeValue
' Make sure it's not zero
If Format(datTimeValue, "hh:mm:nn") = "00:00:00" Then
MsgBox "You must set the starting time to something other than
zero."
Exit Sub
End If
' Turn on the timer interval
Me.TimerInterval = 1000
' Enable the other two buttons
Me.cmdPause.Enabled = True
Me.cmdStop.Enabled = True
End Sub

Private Sub Form_Timer()
Dim datTimeValue As Date
' Grab the current value
datTimeValue = TimeValue(Me.txtClock)
' Subtract 1 second
datTimeValue = datTimeValue - #00:00:01#
' Update the visual
Me.txtClock = datTimeValue
' If decremented to 0,
If Format(datTimeValue, "hh:mm:nn") = "00:00:00" Then
' Turn off the timer
Me.TimerInterval = 0
' Make a sound
Beep
' Put the focus on the text box
Me.txtClock.SetFocus
' Disable the Pause and Stop buttons
Me.cmdPause.Enabled = False
Me.cmdStop.Enabled = False
End If
End Sub

Private Sub cmdPause_Click()
' Stop the timer
Me.TimerInterval = 0
End Sub

Private Sub cmdStop_Click()
' Stop the timer
Me.TimerInterval = 0
' Clear the text box
Me.txtClock = #00:00:00#
' Put the focus there
Me.txtClock.SetFocus
' Disable pause and stop
Me.cmdPause.Enabled = False
Me.cmdStop.Enabled = False
End Sub

That should get you startedŠ

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

-----Original Message-----
From: Bill Singer <Bill.Singer@at-group.net>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Friday, November 30, 2012 5:33 PM
To: <MS_Access_Professionals@yahoogroups.com>
Subject: RE: [MS_AccessPros] Timer

John,

Well I guess that is bad news and good news. I was hoping for some type of
"Clock" function. The good news is that it can still be done. Your
question at the end of your email made me laugh. Absolutely I will need
help writing that code! Anything you can send my way would be great.

Thanks,

Bill Singer

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Friday, November 30, 2012 9:59 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Timer

Bill-

If you just want a digital countdown, you can build your own with a Text
Box and the Timer event. Enter the total time you want to start at in the
text box, then click a Start button. Code behind the Start button would
then set the form's TimerInterval property to 1000 (1000 milliseconds = 1
second) to start the clock. In your On Timer event procedure, subtract 1
second from the clock value. You could also provide a Pause button (set
TimerInterval to 0 to stop it from firing) and a Stop button (set
TimerInterval to 0 and set the Text Box to 0). Need some help writing the
code?

The other alternative is to search for a clock or countdown ActiveX
control on the web.

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

-----Original Message-----
From: Bill Singer <Bill.Singer@at-group.net
<mailto:Bill.Singer%40at-group.net> >
Reply-To: <MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> >
Date: Friday, November 30, 2012 4:32 PM
To: <MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> >
Subject: [MS_AccessPros] Timer

I am wondering if access has a clock function or something that would work
like a game clock in a football game. I want to set the time, click the
start button and then have the clock start counting down.

Thanks,

Bill

MN

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

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

Yahoo! Groups Links

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

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

Yahoo! Groups Links

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (4)
Recent Activity:
.

__,_._,___

Re: [MS_AccessPros] Re: Using UPDATE to change data in a table via combobox and command button

 

Tony-

Ah, well, you have to EXECUTE the query:

Private Sub commandVoid_Click()
Dim strSQL As String

If MsgBox("Are you sure you want to void this ticket?", vbYesNo) =
vbYes Then
strSQL = "UPDATE tableWarehouseData " & _
"SET Status = '" & V & "' " & _
"WHERE Ticket = " & Me.comboVoidTicket
CurrentDB.Execute strSQL, dbFailOnError
End If
End Sub

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

-----Original Message-----
From: Tony <GKillah36@aol.com>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Friday, November 30, 2012 5:38 PM
To: <MS_Access_Professionals@yahoogroups.com>
Subject: [MS_AccessPros] Re: Using UPDATE to change data in a table via
combobox and command button

Ahh, okay. Thanks John. I don't get the error anymore, but it doesn't
actually change the value if the status field to A for those tickets.
Very weird.

And ticket is a numeric field FYI.

Here is what the code looks like now:

Private Sub commandVoid_Click()

Dim strSQL As String

If MsgBox("Are you sure you want to void this ticket?", vbYesNo) =
vbYes Then
strSQL = "UPDATE tableWarehouseData " & _
"SET Status = '" & V & "' " & _
"WHERE Ticket = " & Me.comboVoidTicket
End If
End Sub

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...>
wrote:
>
> Tony-
>
> Your quotes are all messed up. Try this:
>
> strSQL = "UPDATE tableWarehouseData " & _
> "SET Status = '" & V & "' " & _
> "WHERE ticket = '" & Me.comboVoidTicket & "'"
>
>
> That assumes that ticket is a text field. If not, then do:
>
> strSQL = "UPDATE tableWarehouseData " & _
> "SET Status = '" & V & "' " & _
> "WHERE ticket = " & Me.comboVoidTicket
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
>
>
>
> -----Original Message-----
> From: Gkillah36 <GKillah36@...>
> Reply-To: <MS_Access_Professionals@yahoogroups.com>
> Date: Friday, November 30, 2012 6:17 AM
> To: <MS_Access_Professionals@yahoogroups.com>
> Subject: Re: [MS_AccessPros] Using UPDATE to change data in a table via
> combobox and command button
>
> V just comes from me. I just want to have all of the Status field values
> change to V for the ticket that the user selects. The Status field is
> text.
>
> Thanks,
> Tony
>
>
>
>
>
>
>
> -----Original Message-----
> From: Duane Hookom <duanehookom@...>
> To: Access Professionals Yahoo Group
> <ms_access_professionals@yahoogroups.com>
> Sent: Fri, Nov 30, 2012 12:15 am
> Subject: RE: [MS_AccessPros] Using UPDATE to change data in a table via
> combobox and command button
>
>
>
>
>
> Where does V come from? Is the Status field text or numeric?
>
> Learn how to implement
> Debug.Print strSQL
> So you can troubleshoot on your own.
>
> Duane Hookom MVP
> MS Access
>
> > From: GKillah36@...
> >
> > Hi all,
> >
> > Please see my VB below:
> >
> > Private Sub commandVoid_Click()
> >
> > Dim strSQL As String
> >
> > If MsgBox("Are you sure you want to void this ticket?", vbYesNo) =
>vbYes
> >Then
> > strSQL = "UPDATE tableWarehouseData " & _
> > "SET Status = " & V & " " & _
> > "WHERE ticket = " & Me.comboVoidTicket & ""
> > CurrentDb.Execute strSQL, dbFailOnError
> > End If
> > End Sub
> >
> >
> > ----------------------------------------
> >
> > This should change the Status field of all tickets in the table
> >tableWarehouseData to V when clicked based on the selected ticket number
> >in the comboVoidTicket combobox. However, I'm getting an error of
> >"Run-time error '3144': Syntax error in UPDATE statement." What am I
> >missing here?
> >
> > Thank you,
> > Tony
> >
> > FYI, I'm using Access 2010.
>
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>

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

Yahoo! Groups Links

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (6)
Recent Activity:
.

__,_._,___

[MS_AccessPros] Changing the source data of a combobox while maintaining current functionality

 

Hi everyone,

Please see formTicketMod in database PracticeNASDatabase_113012b in Files -> AssistanceNeeded. To the very right of that form I have text boxes and comboboxes in place so the user can have that data added to table tableWarehouseData.

I would like to make a slight change though I'm not sure how to go about it. How do I make it so that clicking commandAdd still does the same thing it does currently, but instead of the user selecting a UPC in comboAddUPC, they select a style, color and size just like combobox cboStyleNo at the very left of the form?

Thank you very much!

Tony

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)
Recent Activity:
.

__,_._,___

Re: Bls: [belajar-access] sharing ttg aplikasi surat jalan

 

Ya mas..sebtulnya menu import itu dibuat uk menghindari proses pada menu buat surat jalan.nenu buat surat jalAn hanya digunakan uk edite data ktka ada kesalahan data dari import.jadi ceritanya gni mas,kta sudah pnya progran uk bikin surat jalan.dr program tersebut bisa export data ke excel dn menampilkan field dan data sprti yg sy krmkn pd athacment.nah sedangkhn program sisuja ini uk rekanan yg memasrkn produk dari perusHaan kita,mereka tidak mau iNput satu persatu.inginya data yang dri perushaan kita lgsung bisa diimport,dan mereka hny tinggal cetak surat jalan aja..krn datanya sama saja.permasalahannya apakah bisa kita menampilkan data keterangan barang dengan hanya import kode barang,tanpa harus proses pada combo lis pada form menu surat jalan??

Ok thnks..msh semangat mas..

Sent from my BlackBerry®
powered by Sinyal Kuat INDOSAT

From: Sumiyanto Surabaya <sumiyanto@live.com>
Sender: belajar-access@yahoogroups.com
Date: Fri, 30 Nov 2012 18:03:54 +0800
To: <belajar-access@yahoogroups.com>
ReplyTo: belajar-access@yahoogroups.com
Subject: RE: Bls: [belajar-access] sharing ttg aplikasi surat jalan

 

Bisa saja mas dilengkapi sambil jalan pada form buat surat jalan,  kan di sana ada combo box untuk pilihan. takutnya data yang ada hubungan nya dengan data master di excel nanti tidak konsisten. silakan di coba dahulu. Nanti kirim kemari lagi hasilnya.  

 

Biasanya sih yang kami lakukan kolaborasi dengan data excel, hanya pertama kali insert data master barang. Karena biasanya data ini ribuan item dan jika di entry manual membutuhkan waktu yang sangat lama. Sehingga jika mau implementasi suatu aplikasi bisa mempersingkat waktu. Tetapi jika memasuki entry transaksi ya harus menjalankannya melalui aplikasi. Tidak di by pass melalui insert by query. Demikian masih semangat kan.

 

Salam,

 

Sumiyanto Surabaya

 

http://cakyanto.wordpress.com/

http://mugi.or.id/blogs/sumiyanto/

From: belajar-access@yahoogroups.com [mailto:belajar-access@yahoogroups.com] On Behalf Of Muhamad Safei
Sent: Friday, November 30, 2012 3:25 PM
To: belajar-access@yahoogroups.com
Subject: Bls: Bls: [belajar-access] sharing ttg aplikasi surat jalan

 

harus persis sama ya mas?? kl kita buat fieldnya sama tapi bbrpa datanya dikosongkan bisa tidakk ya.misalnya kita hanya mengisi kpdIdnya saja atau kodebarangnya saja..sementara filed kepada1-kepada4 kita ambil dari tabel master,begitu juga untuk kode barang... bisa tidak ya??

 

 

Thnks atas jawabannya...

 

 


Dari: Sumiyanto Surabaya <sumiyanto@live.com>
Kepada: belajar-access@yahoogroups.com
Dikirim: Jumat, 30 November 2012 6:53
Judul: RE: Bls: [belajar-access] sharing ttg aplikasi surat jalan

 

 

 

 

Lengkap mungkin tidak bisa mas, karena field table hasil import excel tidak sama persis seperti table TblRptCetakSuratJalan seperti gambar diatas

 

Ini query append yang bisa dilakukan;

 

INSERT INTO tblRptCetakSuratJalan ( Tgl, NoPol, sopir, DONo, PONo, SONo, Banyaknya )

SELECT NamaTabel.Tanggal, NamaTabel.NoPol, NamaTabel.Supir, NamaTabel.NoDo, NamaTabel.NoPo, NamaTabel.NoSo, NamaTabel.Qty

FROM NamaTabel;

 

Mungkin hasil query ini solusinya bisa di edit/dilengkapi pada form buat surat jalan, atau menambahkan data pada excel nya, sehingga pada saat di import ke table baru dan di insert akan sesuai.

 

Salam jabat erat selalu dan semoga memberi semangat, meminjam salamnya bang Edy dan cak Hariyanto

 

Sumiyanto Surabaya

 

 

 

 

 

 

From: belajar-access@yahoogroups.com [mailto:belajar-access@yahoogroups.com] On Behalf Of Muhamad Safei
Sent: Thursday, November 29, 2012 10:09 AM
To: belajar-access@yahoogroups.com
Subject: Re: Bls: [belajar-access] sharing ttg aplikasi surat jalan

 

 

Insert ke TblRptCetakSuratJalan pak.bagaimana cra insert update ketabelnya.dan apakah bisa data yg ditampilkan pada tblrptcetaksuratjalan lengkap seprti data sebelumnya??krn tbl trsbt menJadi source uk cetak surat jalan..

Sent from my BlackBerry®
powered by Sinyal Kuat INDOSAT


From: Sumiyanto Surabaya <sumiyanto@yahoo.com>

Sender: belajar-access@yahoogroups.com

Date: Wed, 28 Nov 2012 16:35:42 -0800 (PST)

To: belajar-access@yahoogroups.com<belajar-access@yahoogroups.com>

ReplyTo: belajar-access@yahoogroups.com

Subject: Re: Bls: [belajar-access] sharing ttg aplikasi surat jalan

 

 

Function tersebut hanya meng-import data dari excel, jadi hanya membuat table baru. jadi belum di jadikan sumber data object manapun. nah mungkin bapak bisa menjelaskan, setelah terbentuk table baru ini, mau di insertkan ke table mana.

di sana kan ada tblTrSuratJalan, dan tblRptCetakSuratJalan. baru nanti kita buat query insert/update.

 

 

Thank you | Terima Kasih | Matur Suksema

Sumiyanto

 


From: Muhamad Safei <safeimuhamad@yahoo.com>
To: "belajar-access@yahoogroups.com" <belajar-access@yahoogroups.com>
Sent: Wednesday, 28 November 2012 2:33 PM
Subject: Bls: [belajar-access] sharing ttg aplikasi surat jalan

 

 

Apakah perlu ada perubahan pada tabel databasenya??

 

 



 

 

 

 


Dari: Sumiyanto Surabaya <sumiyanto@live.com>
Kepada: belajar-access@yahoogroups.com
Dikirim: Rabu, 28 November 2012 9:14
Judul: RE: [belajar-access] sharing ttg aplikasi surat jalan

 

 

Karena menggunakan access 2003, bisa di save as data excelnya  ke 2003 juga (data unk import.xls) kemudian jalankan prosedur import data excel sbb;

 

Private Sub Command3_Click()

 

'sesuaikan nama table dan lokasi path data excelnya

DoCmd.TransferSpreadsheet acImport, _

acSpreadsheetTypeExcel8, _

"NamaTabel", _

"C:\Documents and Settings\Yanto\My Documents\Downloads\Compressed\SiSuJa\SiSuJa\data unk import.xls", _

True

 

MsgBox "import DATA excel finish", _

vbOKOnly, "Import"

 

End Sub

 

 

Salam,

 

Sumiyanto Surabaya

 

 

 

 

From: belajar-access@yahoogroups.com [mailto:belajar-access@yahoogroups.com] On Behalf Of Muhamad Safei
Sent: Tuesday, November 27, 2012 9:44 AM
To: belajar-access@yahoogroups.com
Subject: [belajar-access] sharing ttg aplikasi surat jalan [1 Attachment]

 

 

[Attachment(s) from Muhamad Safei included below]

slamt pagi semuanya..saya ada aplikasi surat jalan..saya akan menambahkan menu untuk import untuk memasukan data agar bisa langsung dari excel,tanpa input satu2...mohon masukannya...langkah2 apa saja yang mesti saya lakukan...thnks...file terlampir...

 

 





  

 

 

 

 

 

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (9)
Recent Activity:
SPAM IS PROHIBITED
.

__,_._,___