VBA - New Learner Help Please

Hi,  I am currently writing a database and what i want it to do is when one 
particular field is filled in eg. Cylinder, then it will pick up only 
"cylinder" items.  I have tried this code below but it doesnt seem to work.

Could someone please help me.


Private Sub Job_Installable_AfterUpdate()
'If Job_Installable.[Column](1) = "Cylinder" Then
'    'Forms!OrderForm.ctlSubForm.Form!Controls.NewData
'    'Forms!
'    [tblJob_Consumables SubForm]![Job_Consumable Consumable 
ID].ControlSource = "qlkpConsumableCylinder"
'ElseIf Job_Installable.[Column](1) = "Tank" Then
'    [tblJob_Consumables SubForm]![Job_Consumable Consumable 
ID].ControlSource = "qlkpConsumableTank"
'End If
Me.tblJob_Consumables_subform.Requery

Thanks  Heaps Sarah

0
Utf
12/21/2009 3:38:01 AM
access.gettingstarted 618 articles. 1 followers. Follow

11 Replies
948 Views

Similar Articles

[PageSpeed] 24

Try removing the square brackets around COLUMN (a property of a listbox or 
combobox).

Also you seem to be referencing a subform so you may need to use the following 
syntax to refer to the control on the subform.

Forms![tblJob_Consumables SubForm].Form!
[Job_Consumable Consumable 
                                                              ID]. ControlSource

Private Sub Job_Installable_AfterUpdate()
If Job_Installable.Column(1) = "Cylinder" Then
    Forms![tblJob_Consumables SubForm]![Job_Consumable Consumable 
 

ID].ControlSource = "qlkpConsumableCylinder"
ElseIf Job_Installable.Column(1) = "Tank" Then
    [tblJob_Consumables SubForm]![Job_Consumable Consumable
ID].ControlSource = "qlkpConsumableTank"
End If
Me.tblJob_Consumables_subform.Requery


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Sarah wrote:
> Hi,  I am currently writing a database and what i want it to do is when one 
> particular field is filled in eg. Cylinder, then it will pick up only 
> "cylinder" items.  I have tried this code below but it doesnt seem to work.
> 
> Could someone please help me.
> 
> 
> Private Sub Job_Installable_AfterUpdate()
> 'If Job_Installable.[Column](1) = "Cylinder" Then
> '    'Forms!OrderForm.ctlSubForm.Form!Controls.NewData
> '    'Forms!
> '    [tblJob_Consumables SubForm]![Job_Consumable Consumable 
> ID].ControlSource = "qlkpConsumableCylinder"
> 'ElseIf Job_Installable.[Column](1) = "Tank" Then
> '    [tblJob_Consumables SubForm]![Job_Consumable Consumable 
> ID].ControlSource = "qlkpConsumableTank"
> 'End If
> Me.tblJob_Consumables_subform.Requery
> 
> Thanks  Heaps Sarah
> 
0
John
12/21/2009 11:34:37 AM
Sarah:

I'm puzzled as to why you are changing the ControlSource property of the
Job_Consumable Consumable ID in the subform.  I think what you are probably
trying to do is restrict the items which can be selected in this control to
those which are relevant to whatever is selected in the Job_Installable
control.  Normally this sort of thing would be done by using a combo box for
Job_Consumable Consumable ID and restricting its RowSource to the relevant
items by referencing the Job_Installable control.  In the latter's
AfterUpdate event procedure you'd  then requery the Job_Consumable Consumable
ID control so its list is restricted accordingly.

Just how you'd do this would depend on the table from which the
Job_Consumable Consumable ID is selected.  This would be expected to have
foreign key column which references the table containing the Job_Installable
values.  If you can give us more details of the table which contains the
distinct Job_Consumable Consumable ID values then we can hopefully point you
in the right direction.

Also where is the Job_Installable control.  Is it in the subform, or in the
main parent form?  This will determine just how it is referenced in the
RowSource property. 

Ken Sheridan
Stafford, England

Sarah wrote:
>Hi,  I am currently writing a database and what i want it to do is when one 
>particular field is filled in eg. Cylinder, then it will pick up only 
>"cylinder" items.  I have tried this code below but it doesnt seem to work.
>
>Could someone please help me.
>
>Private Sub Job_Installable_AfterUpdate()
>'If Job_Installable.[Column](1) = "Cylinder" Then
>'    'Forms!OrderForm.ctlSubForm.Form!Controls.NewData
>'    'Forms!
>'    [tblJob_Consumables SubForm]![Job_Consumable Consumable 
>ID].ControlSource = "qlkpConsumableCylinder"
>'ElseIf Job_Installable.[Column](1) = "Tank" Then
>'    [tblJob_Consumables SubForm]![Job_Consumable Consumable 
>ID].ControlSource = "qlkpConsumableTank"
>'End If
>Me.tblJob_Consumables_subform.Requery
>
>Thanks  Heaps Sarah

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200912/1

0
KenSheridan
12/21/2009 11:14:15 PM
Hi Ken,  What i am trying to do is i am writing a quoting database for my 
manager so that it is easy for them to do installation quotes etc.  I have a 
main form which on it has all the contractor details where the job is and 
what the job will consist of, what i want to be able to do is when i pick 
cylinder (i have in the querie / table "installable" if it is a cylinder or a 
tank) if the person puts a cylinder size in or tank size it will 
automatically open another form with all the relevant information for what 
they have picked.  Hope this makes sense?????  I had help with a trainer to 
get the code as per the below but she couldnt even make it work and i cannot 
either.....  It was the best $1500.00 the company ever spent !!!!!
"KenSheridan via AccessMonster.com" wrote:

> Sarah:
> 
> I'm puzzled as to why you are changing the ControlSource property of the
> Job_Consumable Consumable ID in the subform.  I think what you are probably
> trying to do is restrict the items which can be selected in this control to
> those which are relevant to whatever is selected in the Job_Installable
> control.  Normally this sort of thing would be done by using a combo box for
> Job_Consumable Consumable ID and restricting its RowSource to the relevant
> items by referencing the Job_Installable control.  In the latter's
> AfterUpdate event procedure you'd  then requery the Job_Consumable Consumable
> ID control so its list is restricted accordingly.
> 
> Just how you'd do this would depend on the table from which the
> Job_Consumable Consumable ID is selected.  This would be expected to have
> foreign key column which references the table containing the Job_Installable
> values.  If you can give us more details of the table which contains the
> distinct Job_Consumable Consumable ID values then we can hopefully point you
> in the right direction.
> 
> Also where is the Job_Installable control.  Is it in the subform, or in the
> main parent form?  This will determine just how it is referenced in the
> RowSource property. 
> 
> Ken Sheridan
> Stafford, England
> 
> Sarah wrote:
> >Hi,  I am currently writing a database and what i want it to do is when one 
> >particular field is filled in eg. Cylinder, then it will pick up only 
> >"cylinder" items.  I have tried this code below but it doesnt seem to work.
> >
> >Could someone please help me.
> >
> >Private Sub Job_Installable_AfterUpdate()
> >'If Job_Installable.[Column](1) = "Cylinder" Then
> >'    'Forms!OrderForm.ctlSubForm.Form!Controls.NewData
> >'    'Forms!
> >'    [tblJob_Consumables SubForm]![Job_Consumable Consumable 
> >ID].ControlSource = "qlkpConsumableCylinder"
> >'ElseIf Job_Installable.[Column](1) = "Tank" Then
> >'    [tblJob_Consumables SubForm]![Job_Consumable Consumable 
> >ID].ControlSource = "qlkpConsumableTank"
> >'End If
> >Me.tblJob_Consumables_subform.Requery
> >
> >Thanks  Heaps Sarah
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200912/1
> 
> .
> 
0
Utf
1/7/2010 3:25:01 AM
Sarah:

I think we'll need more information from you to take this forward:

1.  The first question is whether the form you are trying to open is simply
to display data on the selected item to the user so they can then use that in
preparing a quote, or whether its to insert a new row into a table which will
then be the basis for generating the quote.

2.  Before considering the forms however, its important to be clear about the
underlying 'logical model', i.e. the tables involved and how they relate to
each other, so the more detail you can give us on that the better we'll be
able to advise you.

3.  But, going back even further, the 'logical model' is precisely that, a
model of the underlying real world situation in terms of the entity types
involved and how they relate to each other, so as full a description as
possible of what you are trying to achieve in real world terms will help us
in advising on what is an appropriate logical model.  The key to a successful
relational database is getting this logical model right; get it right and the
interface will fall into place naturally, but get it wrong and you'll end up
jumping through hoops for ever more to work around flaws in the model.

Ken Sheridan
Stafford, England

Sarah wrote:
>Hi Ken,  What i am trying to do is i am writing a quoting database for my 
>manager so that it is easy for them to do installation quotes etc.  I have a 
>main form which on it has all the contractor details where the job is and 
>what the job will consist of, what i want to be able to do is when i pick 
>cylinder (i have in the querie / table "installable" if it is a cylinder or a 
>tank) if the person puts a cylinder size in or tank size it will 
>automatically open another form with all the relevant information for what 
>they have picked.  Hope this makes sense?????  I had help with a trainer to 
>get the code as per the below but she couldnt even make it work and i cannot 
>either.....  It was the best $1500.00 the company ever spent !!!!!
>
>> Sarah:
>> 
>[quoted text clipped - 41 lines]
>> >
>> >Thanks  Heaps Sarah
!
>
>> Sarah:
>> 
>[quoted text clipped - 41 lines]
>> >
>> >Thanks  Heaps Sarah

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

0
KenSheridan
1/7/2010 12:17:28 PM
Hi Ken,

The first page of the quote has, A Job No. , Description, Requestor, Date, 
Location, Klms, and Installables (in the Installables you can pick if it is a 
cylinder or tank via a drop down box). Depending on what is picked i would 
like it to open a "consumable form" which either relates to Tanks or 
Cylinders. eg, parts that need to be added into the quote. I have a different 
form for "tank consumables" and another one "cylinder consumables" (hence the 
code), once this form is picked the person will have a list of items that 
they will be needed to do the installation, they will add to the qty in the 
form (they will only use what is there and cannot add anything new) eg, There 
could be 5 pens to be used and 1 ruler etc and the remainder of the items 
will not need to be used, so it will say zero. After all information is added 
in it will be made into a report so that this can be emailed / printed and 
saved so that the sales person know how much the installation will cost.  

With regard to the Tables etc, i have used the "normalise table" so that is 
spilt with the description qty and another table with description qty valve 
and Catagory (this field has either Cylinder or Tank written next to it so 
that when the person picks from the "installable" (in the main form) it knows 
if it is a cylinder or tank item which is what i want the code to do is open 
the form for which it relates to.  

i have tested the queries and they do what they are meant to do, 

I hope this answer's all your questions??????  Thank you so much for taking 
the time to help me out.

Cheers
Sarah Johnston
Melbourne Australia

"KenSheridan via AccessMonster.com" wrote:

> Sarah:
> 
> I think we'll need more information from you to take this forward:
> 
> 1.  The first question is whether the form you are trying to open is simply
> to display data on the selected item to the user so they can then use that in
> preparing a quote, or whether its to insert a new row into a table which will
> then be the basis for generating the quote.
> 
> 2.  Before considering the forms however, its important to be clear about the
> underlying 'logical model', i.e. the tables involved and how they relate to
> each other, so the more detail you can give us on that the better we'll be
> able to advise you.
> 
> 3.  But, going back even further, the 'logical model' is precisely that, a
> model of the underlying real world situation in terms of the entity types
> involved and how they relate to each other, so as full a description as
> possible of what you are trying to achieve in real world terms will help us
> in advising on what is an appropriate logical model.  The key to a successful
> relational database is getting this logical model right; get it right and the
> interface will fall into place naturally, but get it wrong and you'll end up
> jumping through hoops for ever more to work around flaws in the model.
> 
> Ken Sheridan
> Stafford, England
> 
> Sarah wrote:
> >Hi Ken,  What i am trying to do is i am writing a quoting database for my 
> >manager so that it is easy for them to do installation quotes etc.  I have a 
> >main form which on it has all the contractor details where the job is and 
> >what the job will consist of, what i want to be able to do is when i pick 
> >cylinder (i have in the querie / table "installable" if it is a cylinder or a 
> >tank) if the person puts a cylinder size in or tank size it will 
> >automatically open another form with all the relevant information for what 
> >they have picked.  Hope this makes sense?????  I had help with a trainer to 
> >get the code as per the below but she couldnt even make it work and i cannot 
> >either.....  It was the best $1500.00 the company ever spent !!!!!
> >
> >> Sarah:
> >> 
> >[quoted text clipped - 41 lines]
> >> >
> >> >Thanks  Heaps Sarah
> !
> >
> >> Sarah:
> >> 
> >[quoted text clipped - 41 lines]
> >> >
> >> >Thanks  Heaps Sarah
> 
> -- 
> Message posted via http://www.accessmonster.com
> 
> .
> 
0
Utf
1/10/2010 11:50:01 PM
Sarah:

Opening the relevant form should be simple enough as its just a case of
opening whichever is the appropriate one for whatever is selected in
Job_Installable combo box on the main form.  From the code in your original
post it looks like the combo box's value is a hidden first column, presumably
a numeric ID value, as you are referencing its second column.   So simply to
open a form the code in the combo box's AfterUpdate event procedure would be
along these lines;

Dim ctrl As Control
Dim strForm As String

Set ctrl = Me.ActiveControl

' first make sure a selection has been made
If Not IsNull(ctrl) Then
    If ctrl.Column(1) = "Tank" Then
        strForm = "frmTankConsumamables"
    Else
        strForm = "frmCylinderConsumamables"
    End If
    DoCmd.OpenForm strForm
End If

However, that would simply give you a form listing the relevant items,
whereas I'd have envisaged that what you'd need to do is create a set of
related records in another table, which together with the record being
created in the main form would constitute the quote, i.e. you'd have one
record in the main Quotes table and multiple records, one per consumable, in
a related QuoteConsumables table with each record having two foreign key
columns, one referencing the primary key of the Quotes table (JobNo say), the
other referencing the primary key of a Consumables table (together these
columns would be the table's composite primary key) and non-key columns for
the quantity and unit price.

In this sort of situation what I'd normally foresee happening is that, when
Tank or Cylinder is selected in the combo box on the main quote form this
would execute some code which would insert the relevant number of rows into
the QuoteConsumables table, each having the same JobNo as the main form's
current record and a different value in the consumable foreign key column,
and by default zero in the quantity column.  This would mean the code
executing an 'append' query which selects all the relevant primary key values
from those rows in the Consumables table relevant to Tank or Cylinder and
inserts these into QuoteConsumables along with the current JobNo, the unit
price and a zero Quantity.

Then the relevant form would be opened to show the newly created records in
QuoteConsumables, filtering the form to the current JobNo.  This would mean
that you would not need separate forms for Tank or Cylinder as it would
already be filtered to the relevant items.  For this the code would now be
along these lines:

    Dim cmd As ADODB.Command
    Dim strSQL As String
    
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandType = adCmdText

    ' ensure current quote record is saved before inserting
    ' rows into related table
    Me.Dirty = False
    
    ' insert new rows into QuoteConsumables table
    strSQL = "INSERT INTO QuoteConsumables " & _
         "(JobNo, ConsumableID, UnitPrice, Quantity) " & _
         "SELECT " & Me.JobNo & ", UnitPrice,  ConsumableID, 0 " & _
         "FROM Consumables WHERE Category = " & Me.Job_Installable

    cmd.CommandText = strSQL
    cmd.Execute

    ' open form at newly inserted records for user
    ' to insert quantities
    DoCmd.OpenForm "frmQuoteConsumables", _
        WhereCondition:="JobNo = " & Me.JobNo

As an alternative to opening a separate frmQuoteConsumables form the
consumables could be listed in a continuous forms view subform within the
main quote form.  You'd then just have to requery the subform in the code
after inserting the new rows into QuoteConsumables.

I've assumed in the above that QuteNumber is a nember data type, and thet the
Category column in Consumables is a number data type referencing the numeric
primary key of a Categories table.

So diagrammatically the model would look like this:

Quotes----<QuoteConsumables >----Consumables>-----Categories

where the < and > signs represent the 'many' side of each relationship.  From
this you can see that QuoteConsumables in fact models a many-to-many
relationship between Quotes and Consumables, i.e. its what's colloquially
known as a 'junction' table.

Creating a quote report is a simple task of course; its just a question of
basing the report on a query which joins the necessary tables and grouping it
by JobNo, putting the main quote data in the group header, and the
consumables in the detail section.

Ken Sheridan
Stafford, England

Sarah wrote:
>Hi Ken,
>
>The first page of the quote has, A Job No. , Description, Requestor, Date, 
>Location, Klms, and Installables (in the Installables you can pick if it is a 
>cylinder or tank via a drop down box). Depending on what is picked i would 
>like it to open a "consumable form" which either relates to Tanks or 
>Cylinders. eg, parts that need to be added into the quote. I have a different 
>form for "tank consumables" and another one "cylinder consumables" (hence the 
>code), once this form is picked the person will have a list of items that 
>they will be needed to do the installation, they will add to the qty in the 
>form (they will only use what is there and cannot add anything new) eg, There 
>could be 5 pens to be used and 1 ruler etc and the remainder of the items 
>will not need to be used, so it will say zero. After all information is added 
>in it will be made into a report so that this can be emailed / printed and 
>saved so that the sales person know how much the installation will cost.  
>
>With regard to the Tables etc, i have used the "normalise table" so that is 
>spilt with the description qty and another table with description qty valve 
>and Catagory (this field has either Cylinder or Tank written next to it so 
>that when the person picks from the "installable" (in the main form) it knows 
>if it is a cylinder or tank item which is what i want the code to do is open 
>the form for which it relates to.  
>
>i have tested the queries and they do what they are meant to do, 
>
>I hope this answer's all your questions??????  Thank you so much for taking 
>the time to help me out.
>
>Cheers
>Sarah Johnston
>Melbourne Australia
>
>> Sarah:
>> 
>[quoted text clipped - 45 lines]
>> >> >
>> >> >Thanks  Heaps Sarah

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201001/1

0
KenSheridan
1/11/2010 6:38:22 PM
Hi Ken,

You are amazing !!!!!!!!!! Thank you so much for all your help... 
It works (not that i doubted you)....
I am sure i will be asking more questions but at the moment it is all going 
ok.  Thanks so much
Cheers
Sarah
Melbourne Australia

"KenSheridan via AccessMonster.com" wrote:

> Sarah:
> 
> Opening the relevant form should be simple enough as its just a case of
> opening whichever is the appropriate one for whatever is selected in
> Job_Installable combo box on the main form.  From the code in your original
> post it looks like the combo box's value is a hidden first column, presumably
> a numeric ID value, as you are referencing its second column.   So simply to
> open a form the code in the combo box's AfterUpdate event procedure would be
> along these lines;
> 
> Dim ctrl As Control
> Dim strForm As String
> 
> Set ctrl = Me.ActiveControl
> 
> ' first make sure a selection has been made
> If Not IsNull(ctrl) Then
>     If ctrl.Column(1) = "Tank" Then
>         strForm = "frmTankConsumamables"
>     Else
>         strForm = "frmCylinderConsumamables"
>     End If
>     DoCmd.OpenForm strForm
> End If
> 
> However, that would simply give you a form listing the relevant items,
> whereas I'd have envisaged that what you'd need to do is create a set of
> related records in another table, which together with the record being
> created in the main form would constitute the quote, i.e. you'd have one
> record in the main Quotes table and multiple records, one per consumable, in
> a related QuoteConsumables table with each record having two foreign key
> columns, one referencing the primary key of the Quotes table (JobNo say), the
> other referencing the primary key of a Consumables table (together these
> columns would be the table's composite primary key) and non-key columns for
> the quantity and unit price.
> 
> In this sort of situation what I'd normally foresee happening is that, when
> Tank or Cylinder is selected in the combo box on the main quote form this
> would execute some code which would insert the relevant number of rows into
> the QuoteConsumables table, each having the same JobNo as the main form's
> current record and a different value in the consumable foreign key column,
> and by default zero in the quantity column.  This would mean the code
> executing an 'append' query which selects all the relevant primary key values
> from those rows in the Consumables table relevant to Tank or Cylinder and
> inserts these into QuoteConsumables along with the current JobNo, the unit
> price and a zero Quantity.
> 
> Then the relevant form would be opened to show the newly created records in
> QuoteConsumables, filtering the form to the current JobNo.  This would mean
> that you would not need separate forms for Tank or Cylinder as it would
> already be filtered to the relevant items.  For this the code would now be
> along these lines:
> 
>     Dim cmd As ADODB.Command
>     Dim strSQL As String
>     
>     Set cmd = New ADODB.Command
>     cmd.ActiveConnection = CurrentProject.Connection
>     cmd.CommandType = adCmdText
> 
>     ' ensure current quote record is saved before inserting
>     ' rows into related table
>     Me.Dirty = False
>     
>     ' insert new rows into QuoteConsumables table
>     strSQL = "INSERT INTO QuoteConsumables " & _
>          "(JobNo, ConsumableID, UnitPrice, Quantity) " & _
>          "SELECT " & Me.JobNo & ", UnitPrice,  ConsumableID, 0 " & _
>          "FROM Consumables WHERE Category = " & Me.Job_Installable
> 
>     cmd.CommandText = strSQL
>     cmd.Execute
> 
>     ' open form at newly inserted records for user
>     ' to insert quantities
>     DoCmd.OpenForm "frmQuoteConsumables", _
>         WhereCondition:="JobNo = " & Me.JobNo
> 
> As an alternative to opening a separate frmQuoteConsumables form the
> consumables could be listed in a continuous forms view subform within the
> main quote form.  You'd then just have to requery the subform in the code
> after inserting the new rows into QuoteConsumables.
> 
> I've assumed in the above that QuteNumber is a nember data type, and thet the
> Category column in Consumables is a number data type referencing the numeric
> primary key of a Categories table.
> 
> So diagrammatically the model would look like this:
> 
> Quotes----<QuoteConsumables >----Consumables>-----Categories
> 
> where the < and > signs represent the 'many' side of each relationship.  From
> this you can see that QuoteConsumables in fact models a many-to-many
> relationship between Quotes and Consumables, i.e. its what's colloquially
> known as a 'junction' table.
> 
> Creating a quote report is a simple task of course; its just a question of
> basing the report on a query which joins the necessary tables and grouping it
> by JobNo, putting the main quote data in the group header, and the
> consumables in the detail section.
> 
> Ken Sheridan
> Stafford, England
> 
> Sarah wrote:
> >Hi Ken,
> >
> >The first page of the quote has, A Job No. , Description, Requestor, Date, 
> >Location, Klms, and Installables (in the Installables you can pick if it is a 
> >cylinder or tank via a drop down box). Depending on what is picked i would 
> >like it to open a "consumable form" which either relates to Tanks or 
> >Cylinders. eg, parts that need to be added into the quote. I have a different 
> >form for "tank consumables" and another one "cylinder consumables" (hence the 
> >code), once this form is picked the person will have a list of items that 
> >they will be needed to do the installation, they will add to the qty in the 
> >form (they will only use what is there and cannot add anything new) eg, There 
> >could be 5 pens to be used and 1 ruler etc and the remainder of the items 
> >will not need to be used, so it will say zero. After all information is added 
> >in it will be made into a report so that this can be emailed / printed and 
> >saved so that the sales person know how much the installation will cost.  
> >
> >With regard to the Tables etc, i have used the "normalise table" so that is 
> >spilt with the description qty and another table with description qty valve 
> >and Catagory (this field has either Cylinder or Tank written next to it so 
> >that when the person picks from the "installable" (in the main form) it knows 
> >if it is a cylinder or tank item which is what i want the code to do is open 
> >the form for which it relates to.  
> >
> >i have tested the queries and they do what they are meant to do, 
> >
> >I hope this answer's all your questions??????  Thank you so much for taking 
> >the time to help me out.
> >
> >Cheers
> >Sarah Johnston
> >Melbourne Australia
> >
> >> Sarah:
> >> 
> >[quoted text clipped - 45 lines]
> >> >> >
> >> >> >Thanks  Heaps Sarah
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201001/1
> 
> .
> 
0
Utf
1/13/2010 12:57:01 AM
Hi Ken,

I have been putting this into my database as per your advise below, the 
first part of the code is working but not the second part?  What i have done 
is written all the first part, and this come's up in my database with either 
Cylinder or Tank which is correct but it still will not open the form that i 
need.  I have put both code's in together one after the other without a break 
but nothing happening?  Could you please help again... 
Thanks Sarah

"Sarah" wrote:

> Hi Ken,
> 
> You are amazing !!!!!!!!!! Thank you so much for all your help... 
> It works (not that i doubted you)....
> I am sure i will be asking more questions but at the moment it is all going 
> ok.  Thanks so much
> Cheers
> Sarah
> Melbourne Australia
> 
> "KenSheridan via AccessMonster.com" wrote:
> 
> > Sarah:
> > 
> > Opening the relevant form should be simple enough as its just a case of
> > opening whichever is the appropriate one for whatever is selected in
> > Job_Installable combo box on the main form.  From the code in your original
> > post it looks like the combo box's value is a hidden first column, presumably
> > a numeric ID value, as you are referencing its second column.   So simply to
> > open a form the code in the combo box's AfterUpdate event procedure would be
> > along these lines;
> > 
> > Dim ctrl As Control
> > Dim strForm As String
> > 
> > Set ctrl = Me.ActiveControl
> > 
> > ' first make sure a selection has been made
> > If Not IsNull(ctrl) Then
> >     If ctrl.Column(1) = "Tank" Then
> >         strForm = "frmTankConsumamables"
> >     Else
> >         strForm = "frmCylinderConsumamables"
> >     End If
> >     DoCmd.OpenForm strForm
> > End If
> > 
> > However, that would simply give you a form listing the relevant items,
> > whereas I'd have envisaged that what you'd need to do is create a set of
> > related records in another table, which together with the record being
> > created in the main form would constitute the quote, i.e. you'd have one
> > record in the main Quotes table and multiple records, one per consumable, in
> > a related QuoteConsumables table with each record having two foreign key
> > columns, one referencing the primary key of the Quotes table (JobNo say), the
> > other referencing the primary key of a Consumables table (together these
> > columns would be the table's composite primary key) and non-key columns for
> > the quantity and unit price.
> > 
> > In this sort of situation what I'd normally foresee happening is that, when
> > Tank or Cylinder is selected in the combo box on the main quote form this
> > would execute some code which would insert the relevant number of rows into
> > the QuoteConsumables table, each having the same JobNo as the main form's
> > current record and a different value in the consumable foreign key column,
> > and by default zero in the quantity column.  This would mean the code
> > executing an 'append' query which selects all the relevant primary key values
> > from those rows in the Consumables table relevant to Tank or Cylinder and
> > inserts these into QuoteConsumables along with the current JobNo, the unit
> > price and a zero Quantity.
> > 
> > Then the relevant form would be opened to show the newly created records in
> > QuoteConsumables, filtering the form to the current JobNo.  This would mean
> > that you would not need separate forms for Tank or Cylinder as it would
> > already be filtered to the relevant items.  For this the code would now be
> > along these lines:
> > 
> >     Dim cmd As ADODB.Command
> >     Dim strSQL As String
> >     
> >     Set cmd = New ADODB.Command
> >     cmd.ActiveConnection = CurrentProject.Connection
> >     cmd.CommandType = adCmdText
> > 
> >     ' ensure current quote record is saved before inserting
> >     ' rows into related table
> >     Me.Dirty = False
> >     
> >     ' insert new rows into QuoteConsumables table
> >     strSQL = "INSERT INTO QuoteConsumables " & _
> >          "(JobNo, ConsumableID, UnitPrice, Quantity) " & _
> >          "SELECT " & Me.JobNo & ", UnitPrice,  ConsumableID, 0 " & _
> >          "FROM Consumables WHERE Category = " & Me.Job_Installable
> > 
> >     cmd.CommandText = strSQL
> >     cmd.Execute
> > 
> >     ' open form at newly inserted records for user
> >     ' to insert quantities
> >     DoCmd.OpenForm "frmQuoteConsumables", _
> >         WhereCondition:="JobNo = " & Me.JobNo
> > 
> > As an alternative to opening a separate frmQuoteConsumables form the
> > consumables could be listed in a continuous forms view subform within the
> > main quote form.  You'd then just have to requery the subform in the code
> > after inserting the new rows into QuoteConsumables.
> > 
> > I've assumed in the above that QuteNumber is a nember data type, and thet the
> > Category column in Consumables is a number data type referencing the numeric
> > primary key of a Categories table.
> > 
> > So diagrammatically the model would look like this:
> > 
> > Quotes----<QuoteConsumables >----Consumables>-----Categories
> > 
> > where the < and > signs represent the 'many' side of each relationship.  From
> > this you can see that QuoteConsumables in fact models a many-to-many
> > relationship between Quotes and Consumables, i.e. its what's colloquially
> > known as a 'junction' table.
> > 
> > Creating a quote report is a simple task of course; its just a question of
> > basing the report on a query which joins the necessary tables and grouping it
> > by JobNo, putting the main quote data in the group header, and the
> > consumables in the detail section.
> > 
> > Ken Sheridan
> > Stafford, England
> > 
> > Sarah wrote:
> > >Hi Ken,
> > >
> > >The first page of the quote has, A Job No. , Description, Requestor, Date, 
> > >Location, Klms, and Installables (in the Installables you can pick if it is a 
> > >cylinder or tank via a drop down box). Depending on what is picked i would 
> > >like it to open a "consumable form" which either relates to Tanks or 
> > >Cylinders. eg, parts that need to be added into the quote. I have a different 
> > >form for "tank consumables" and another one "cylinder consumables" (hence the 
> > >code), once this form is picked the person will have a list of items that 
> > >they will be needed to do the installation, they will add to the qty in the 
> > >form (they will only use what is there and cannot add anything new) eg, There 
> > >could be 5 pens to be used and 1 ruler etc and the remainder of the items 
> > >will not need to be used, so it will say zero. After all information is added 
> > >in it will be made into a report so that this can be emailed / printed and 
> > >saved so that the sales person know how much the installation will cost.  
> > >
> > >With regard to the Tables etc, i have used the "normalise table" so that is 
> > >spilt with the description qty and another table with description qty valve 
> > >and Catagory (this field has either Cylinder or Tank written next to it so 
> > >that when the person picks from the "installable" (in the main form) it knows 
> > >if it is a cylinder or tank item which is what i want the code to do is open 
> > >the form for which it relates to.  
> > >
> > >i have tested the queries and they do what they are meant to do, 
> > >
> > >I hope this answer's all your questions??????  Thank you so much for taking 
> > >the time to help me out.
> > >
> > >Cheers
> > >Sarah Johnston
> > >Melbourne Australia
> > >
> > >> Sarah:
> > >> 
> > >[quoted text clipped - 45 lines]
> > >> >> >
> > >> >> >Thanks  Heaps Sarah
> > 
> > -- 
> > Message posted via AccessMonster.com
> > http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201001/1
> > 
> > .
> > 
0
Utf
2/1/2010 4:20:01 AM
Sarah :

If you've set up the tables according to the model I described:

Quotes----<QuoteConsumables >----Consumables>-----Categories

then the first piece of code is redundant as you only now need the one form,
based on the QuoteConsumables table; its only the second code snippet you
need:

   Dim cmd As ADODB.Command
   Dim strSQL As String
   
   Set cmd = New ADODB.Command
   cmd.ActiveConnection = CurrentProject.Connection
   cmd.CommandType = adCmdText

   ' ensure current quote record is saved before inserting
   ' rows into related table
   Me.Dirty = False
   
   ' insert new rows into QuoteConsumables table
   strSQL = "INSERT INTO QuoteConsumables " & _
        "(JobNo, ConsumableID, UnitPrice, Quantity) " & _
        "SELECT " & Me.JobNo & ", UnitPrice,  ConsumableID, 0 " & _
        "FROM Consumables WHERE Category = " & Me.Job_Installable

   cmd.CommandText = strSQL
   cmd.Execute

   ' open form at newly inserted records for user
   ' to insert quantities
   DoCmd.OpenForm "frmQuoteConsumables", _
       WhereCondition:="JobNo = " & Me.JobNo

This will go in the combo box's AfterUpdate event procedure.  The way it
works is that it first builds an SQL statement to insert a set of rows in to
the QuoteConsumables table, one row for each item in the Consumables table
where the category is whatever is selected in the combo box, i.e. tank or
cylinder.  This is then executed (this is in effect the same as executing an
'append' query).  Next it open a form based on the QuoteConsumables table,
filtering the form to the current job number.

You need to be sure that your table and columns names and the data types of
the columns are correctly reflected in the code, and that the name of the
form being opened, frmQuoteConsumables in the above example, is the same as
that in the code.

If its still not playing ball, then you need to debug it.  Access provides
tools for this, but the simplest thing would be to set a breakpoint in the
code on the strSQL = "INSERT False line etc line.  Then open the form and
create a new job record, and select an item in the combo box.  The code will
break at that line and you can step into it line by line by pressing the F8
key.  You can then see if all the lines are being correctly executed and
check the value of variables at each stage.

Obviously we can't debug the code at this distance, but one way we might be
able to help is if you add the following line:

Debug.Print strSQL 

immediately after the strSQL = "INSERT False line etc line. (the four lines
in the code are really a single line of code, broken with the underscore
continuation character for easier readability).  Then open the form and
create a new job record, and select an item in the combo box.  You should
then find that the value of the strSQL variable has been written to the debug
window (aka immediate window – press Ctrl + G to open it).  Copy this and
post it here.  This will help us to see if there  is any problem with the SQL,
or at least enable us to ask you some more questions. 

Ken Sheridan
Stafford, England

Sarah wrote:
>Hi Ken,
>
>I have been putting this into my database as per your advise below, the 
>first part of the code is working but not the second part?  What i have done 
>is written all the first part, and this come's up in my database with either 
>Cylinder or Tank which is correct but it still will not open the form that i 
>need.  I have put both code's in together one after the other without a break 
>but nothing happening?  Could you please help again... 
>Thanks Sarah
>
>> Hi Ken,
>> 
>[quoted text clipped - 147 lines]
>> > >> >> >
>> > >> >> >Thanks  Heaps Sarah

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201002/1

0
KenSheridan
2/1/2010 1:00:31 PM
Hi Ken,

Sorry to bother you once again.............  I have written the code etc as 
per your below advise.  I am now having another problem when i put the code 
in via build code event in the actual properties part it does not allow me to 
click on "event Procedure"????  Does this have somthing to do with the code 
that i have in properties Control Source Box??? in there i have written =[Job 
installable].[Column].(1)

Thanks Sarah





"KenSheridan via AccessMonster.com" wrote:

> Sarah :
> 
> If you've set up the tables according to the model I described:
> 
> Quotes----<QuoteConsumables >----Consumables>-----Categories
> 
> then the first piece of code is redundant as you only now need the one form,
> based on the QuoteConsumables table; its only the second code snippet you
> need:
> 
>    Dim cmd As ADODB.Command
>    Dim strSQL As String
>    
>    Set cmd = New ADODB.Command
>    cmd.ActiveConnection = CurrentProject.Connection
>    cmd.CommandType = adCmdText
> 
>    ' ensure current quote record is saved before inserting
>    ' rows into related table
>    Me.Dirty = False
>    
>    ' insert new rows into QuoteConsumables table
>    strSQL = "INSERT INTO QuoteConsumables " & _
>         "(JobNo, ConsumableID, UnitPrice, Quantity) " & _
>         "SELECT " & Me.JobNo & ", UnitPrice,  ConsumableID, 0 " & _
>         "FROM Consumables WHERE Category = " & Me.Job_Installable
> 
>    cmd.CommandText = strSQL
>    cmd.Execute
> 
>    ' open form at newly inserted records for user
>    ' to insert quantities
>    DoCmd.OpenForm "frmQuoteConsumables", _
>        WhereCondition:="JobNo = " & Me.JobNo
> 
> This will go in the combo box's AfterUpdate event procedure.  The way it
> works is that it first builds an SQL statement to insert a set of rows in to
> the QuoteConsumables table, one row for each item in the Consumables table
> where the category is whatever is selected in the combo box, i.e. tank or
> cylinder.  This is then executed (this is in effect the same as executing an
> 'append' query).  Next it open a form based on the QuoteConsumables table,
> filtering the form to the current job number.
> 
> You need to be sure that your table and columns names and the data types of
> the columns are correctly reflected in the code, and that the name of the
> form being opened, frmQuoteConsumables in the above example, is the same as
> that in the code.
> 
> If its still not playing ball, then you need to debug it.  Access provides
> tools for this, but the simplest thing would be to set a breakpoint in the
> code on the strSQL = "INSERT False line etc line.  Then open the form and
> create a new job record, and select an item in the combo box.  The code will
> break at that line and you can step into it line by line by pressing the F8
> key.  You can then see if all the lines are being correctly executed and
> check the value of variables at each stage.
> 
> Obviously we can't debug the code at this distance, but one way we might be
> able to help is if you add the following line:
> 
> Debug.Print strSQL 
> 
> immediately after the strSQL = "INSERT False line etc line. (the four lines
> in the code are really a single line of code, broken with the underscore
> continuation character for easier readability).  Then open the form and
> create a new job record, and select an item in the combo box.  You should
> then find that the value of the strSQL variable has been written to the debug
> window (aka immediate window – press Ctrl + G to open it).  Copy this and
> post it here.  This will help us to see if there  is any problem with the SQL,
> or at least enable us to ask you some more questions. 
> 
> Ken Sheridan
> Stafford, England
> 
> Sarah wrote:
> >Hi Ken,
> >
> >I have been putting this into my database as per your advise below, the 
> >first part of the code is working but not the second part?  What i have done 
> >is written all the first part, and this come's up in my database with either 
> >Cylinder or Tank which is correct but it still will not open the form that i 
> >need.  I have put both code's in together one after the other without a break 
> >but nothing happening?  Could you please help again... 
> >Thanks Sarah
> >
> >> Hi Ken,
> >> 
> >[quoted text clipped - 147 lines]
> >> > >> >> >
> >> > >> >> >Thanks  Heaps Sarah
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201002/1
> 
> .
> 
0
Utf
2/2/2010 4:00:01 AM
Sarah:

Shouldn't the combo box be a bound control?  In which case its ControlSource
would be the name of the relevant field in the form's underlying table, which
I think is probably:

[Job_Consumable Consumable ID]

The expression '=[Job installable].[Column].(1)' is used to return the value
in the second column of the combo box, which is the text value as the,
presumably numeric, ID value in the first column is hidden.  Normally you'd
use such an expression as the ControlSource property of a separate text box
control to show the value from a column in a combo box's RowSource which is
visible in the drop down list, but not when an item is selected, e.g. to show
a State when you select a City from a  combo box listing cities and states to
enable you to select the right one where there are two or more cities with
the same name but in different states.

I f you find you get really stuck you can send me the file at:

kenwsheridan<at>yahoo<dot>co<dot>uk

and I'll take a look.  It would need to be in Access 2002 format, so if you
are using a later version you'd need to convert it first.

Ken Sheridan
Stafford, England

Sarah wrote:
>Hi Ken,
>
>Sorry to bother you once again.............  I have written the code etc as 
>per your below advise.  I am now having another problem when i put the code 
>in via build code event in the actual properties part it does not allow me to 
>click on "event Procedure"????  Does this have somthing to do with the code 
>that i have in properties Control Source Box??? in there i have written =[Job 
>installable].[Column].(1)
>
>Thanks Sarah
>
>> Sarah :
>> 
>[quoted text clipped - 84 lines]
>> >> > >> >> >
>> >> > >> >> >Thanks  Heaps Sarah

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

0
KenSheridan
2/2/2010 1:10:32 PM
Reply:

Similar Artilces: