VBA - New Learner Help Please

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/For...arted/201002/1

.

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:

kenwsheridanatyahoodotcodotuk

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