Kamis, 31 Desember 2015

Re: [MS_AccessPros] Re: Dependent combo boxes

 

Bob-


When you reply on the web, be sure to click Show Message History.  I sorta remember what you're talking about.  Did you remove the <<Expr>> as I suggested?  That should fix it.

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 
(Paris, France)



On Dec 31, 2015, at 10:27 PM, bburke@swiftaz.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Sorry John, I don't know how to create the query to get that result.  I tried using the Criteria = CarName but didn't work.

Bob


__._,_.___
Posted by: John Viescas <johnv@msn.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (7)

.

__,_._,___
Diposting oleh di Tidak ada komentar:

[MS_AccessPros] Re: Dependent combo boxes

 

Sorry John, I don't know how to create the query to get that result.  I tried using the Criteria = CarName but didn't work.

Bob

__._,_.___
Posted by: bburke@swiftaz.net
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (6)

.

__,_._,___
Diposting oleh di Tidak ada komentar:

Re: [MS_AccessPros] Error 3265 item not found in this collection

 

Phucon-


What is the SQL of qryUSStates?  If there's no parameter asking for a filter on the state, then that's the problem.

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 
(Paris, France)



On Dec 31, 2015, at 8:49 PM, saigonf7q5@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I copied this Sub (shown below) from a Website and played with it. It spat out a 3265 item not found in this collection, and I could not figure out where caused the trouble.


The procedure will work fine if I remove the line "qdf.Parameters(0) = pstrState". I guess, perhaps that line is not necessary there. But I am wondering why and what causes the error.


"Abbr" is a first field name of the table, it's the state abbreviation.


Phucon



Call RunParameterQuery_DAO (Me!cboState.Column(0))


Public Sub RunParameterQuery_DAO(pstrState As String)
  ' Comments: Runs a query containing parameters
  ' Params  : pstrState       Name of state to select records


  Const cstrQueryName As String = "qryUSstates"
  Dim dbs As DAO.Database
  Dim qdf As DAO.QueryDef
  Dim rst As DAO.Recordset

  Set dbs = CurrentDb()

  Set qdf = dbs.QueryDefs(cstrQueryName)
  qdf.Parameters(0) = pstrState

  'Open recordset on the query
  Set rst = qdf.OpenRecordset()

  Do While Not rst.EOF
    Debug.Print ("Abbr: " & rst![Abbr] & " State: " & rst![USState] & " Capital: " & rst![StateCapital])
    'Debug.Print Me.cboState.Column(0)
    rst.MoveNext
  Loop

  rst.Close
  qdf.Close
  dbs.Close
End Sub


__._,_.___
Posted by: John Viescas <johnv@msn.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (2)

.

__,_._,___
Diposting oleh di Tidak ada komentar:

[MS_AccessPros] Error 3265 item not found in this collection

 

I copied this Sub (shown below) from a Website and played with it. It spat out a 3265 item not found in this collection, and I could not figure out where caused the trouble.


The procedure will work fine if I remove the line "qdf.Parameters(0) = pstrState". I guess, perhaps that line is not necessary there. But I am wondering why and what causes the error.


"Abbr" is a first field name of the table, it's the state abbreviation.


Phucon



Call RunParameterQuery_DAO (Me!cboState.Column(0))


Public Sub RunParameterQuery_DAO(pstrState As String)
  ' Comments: Runs a query containing parameters
  ' Params  : pstrState       Name of state to select records


  Const cstrQueryName As String = "qryUSstates"
  Dim dbs As DAO.Database
  Dim qdf As DAO.QueryDef
  Dim rst As DAO.Recordset

  Set dbs = CurrentDb()

  Set qdf = dbs.QueryDefs(cstrQueryName)
  qdf.Parameters(0) = pstrState

  'Open recordset on the query
  Set rst = qdf.OpenRecordset()

  Do While Not rst.EOF
    Debug.Print ("Abbr: " & rst![Abbr] & " State: " & rst![USState] & " Capital: " & rst![StateCapital])
    'Debug.Print Me.cboState.Column(0)
    rst.MoveNext
  Loop

  rst.Close
  qdf.Close
  dbs.Close
End Sub

__._,_.___
Posted by: saigonf7q5@yahoo.com
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (1)

.

__,_._,___
Diposting oleh di Tidak ada komentar:

Re: [MS_AccessPros] Re: Dependent combo boxes

 

bburke-  (name?)


Looks like you tried to use the Expression Builder to create your SQL - never do that.  It's better to create a query that does what you want, then switch to SQL View and copy what the query builder created.

You need to get rid of <<Expr>> in the two places it appears.  Provided you have the field and table name right, that should fix it.

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 
(Paris, France)



On Dec 31, 2015, at 5:14 PM, bburke@swiftaz.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hello John,

I created a form using the Form Wizard and placed two combo boxes on the form.  I used this expression in the Control Source of the cmbCarNumber:

= SELECT «Expr» [CarNumber] FROM [tblCars] WHERE «Expr» [CarName] = [Forms]![frmCars]![cmbCarName]

I also created a blank form and placed the two combo boxes on that form I received a 'syntax is incorrect' dialog box after both attempts. I missed something, just not sure what.


__._,_.___
Posted by: John Viescas <johnv@msn.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (5)

.

__,_._,___
Diposting oleh di Tidak ada komentar:

[MS_AccessPros] Re: Dependent combo boxes

 

Hello John,

I created a form using the Form Wizard and placed two combo boxes on the form.  I used this expression in the Control Source of the cmbCarNumber:

= SELECT «Expr» [CarNumber] FROM [tblCars] WHERE «Expr» [CarName] = [Forms]![frmCars]![cmbCarName]

I also created a blank form and placed the two combo boxes on that form I received a 'syntax is incorrect' dialog box after both attempts. I missed something, just not sure what.

__._,_.___
Posted by: bburke@swiftaz.net
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (4)

.

__,_._,___
Diposting oleh di Tidak ada komentar:

Re: [MS_AccessPros] To query or not to query, that is the question

 

Dear robejohg (name?)-


If there can be multiple plans associated with a row in tblStockMovement, then you need another table:

tblStockMovementPlans
StockMovement_ID
Plan_ID
.. other fields as appropriate.

Then you need a subform for this table on your form that edits tblStockMovement - linked on StockMovement_ID.  Then a subform within that to record the answers for the questions related to the plan.  I'm thinking tblTheAnswers also needs Plan_ID so that you can link it directly back to tblStockMovementPlans.

I gather that tblThePlan and tblTheQuestions are pre-loaded.  When you set the Plan_ID in tblStockMovementPlans, that can link to tblTheAnswers on that Plan_ID, then you pull the questions in a combo box that has a Row Source from tblTheQuestions filtered on the selected Plan.  The user would select the questions one at a time, then fill in the AnsChkBx and txtTheQuestions fields.  You might consider some code to ensure that all the questions related to a selected plan have an answer before you allow the user to navigate to another Plan or another StockMovement.

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 
(Paris, France)



On Dec 31, 2015, at 1:19 PM, robejohg@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



I am trying to set up a form that I can "justify" the movement of inventory. I  have 5 tables:


The Stock
The Plan
tblTheStock
tblStockMovement
tblThePlan
tblTheQuestions
tblTheAnswers
Stock_ID
StockMovement_ID
Plan_ID
TheQuestions_ID
TheAnswers_ID
txtStockCode
Stock_ID
txtPlanName
Plan_ID
TheQuestions_ID
Various others
txtMovementType
Various others
txtTheQuestions
AnsChkBx (Y/N/NA)

 
Various others

 

 
txtTheAnswer

 
Plan_1

 

 

 

 
Plan_2

 

 

 

 
Plan_???

 

 

 


The goal is:


  • For every StockMovement to have a minimum of 1 Plan.  Would like to have the ability to have more.
  • frmStockMovement
    • tblTheStock fields as required
    • tblStockMovement fields as required
    • Button(s) to open the frmThePlan to update txtTheAnswer or AnsChkBx  as required.
  • frmThePlan would have:
    • txtPlanName (This cannot be modified)
    • txtStockCode (This cannot be modified)
    • Various others from tblTheStock, or tblStockMovement (This cannot be modified)

       

    • sfrm QuestionsAnswers
      • txtTheQuestions (This cannot be modified)
      • txtTheAnswer (This can be changed)

      • AnsChkBx (Y/N/NA) (This can be changed)


My problem is setting up the frmThePlan . I cannot get the sfrm QuestionsAnswers to work. Trying to get:


The Plan:
PlanName
 
The Stock
StockName
 
Various others
tblStockMovement
 

 

 

 
Subform

 

 
txtTheQuestions
txtTheAnswer
AnsChkBx
Question 1(
This
Y
Question 2
that
N
Question …..
The other
N/A


 


Any help would be appreciated.


Thanks




__._,_.___
Posted by: John Viescas <johnv@msn.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (2)

.

__,_._,___
Diposting oleh di Tidak ada komentar:
Langganan: Postingan (Atom)