trouble starting code

[mejia.j88]

I'm having a bit of trouble starting some code.



In my form, i have a drop down box in which the operator can choose a blade to use. this drop down box is [blade_no], next to it, i have another drop down box in which the operator chooses the cut number with the blade (1,2,3,..10,Final Cut). after final cut, the blade is thrown away. this field is called [cut_no], and always stays the same.

i want to write some code that will purge [blade_no] of the blades that have reached their maximum cut.

not sure how to link these two things together?

any help would be greatly appreciated.
ask as many questions as you feel necessary.

-thank you

[Robeen]

You could write a Delete Query.

Start by creating a query that gives you all the records that are at the Final Cut.
When you are convinced that you have all the rows that are at Final Cut, convert the query to a Delete Query [there is a button with an X on it in the Query Design window - just click it].

Now - whenever you run the query - it will delete all rows of data where cut_no is at the Final Cut.

I hope this helps!

[mejia.j88]

That's what i was afraid of, i just wanted to get rid of them in the combo box, to avoid unnecessary clutter for the operators.
it is still necessary for me to have what blade/blade cut was used.

the way im deleting them now is through the "allow value list edits" (under properties).

[Robeen]

If all you want is to prevent the Final Cut blades from displaying in the combo box - then you can change what is displayed in the Combo Box by changing the query in the Row Source [under Data in the Property Sheet].
Select your combo box - click Property Sheet - click Data & then modify the query in the Row Source field.

Are you comfortable writing queries?
Your query for displaying only blade numbers that have not reached Final Cut will say something like this:
Select blade_no from [YourTableName] where cut_no <> "Final Cut";

Hope this helps.

[mejia.j88]

just want to make sure i understand:

so for this combo box i should change the control source to the query i created (called qry_blade_no_order; which includes the primary key ID, Blade_no, Cut_no) ?

the current control source is blade_no from the table i use, tbl_dicing_yield.

then change row source to: Select blade_# from [tbl_dicing_yield] where cut_# <> "Final Cut"

and row_source_type to query/table?

like I said, I'm worried about deleting existing records in the DB.

[Robeen]

Just make the change to your Row Source.

What is the statement in there now?

You will not be deleting anything by changing the Row Source of your Combo Box.
All that will happen when you change the SQL statement in Row Source is that it will change what is displayed by not showing any records that have 'Final Cut'.
It won't DO anything to the DATA in your database.

'Select' SQL statements only display data.

The ones you have to watch out for are the 'Delete' and 'Update' SQL statements. Those are 'Action' queries and they change data in your database.

Hope this helps!

[mejia.j88]

Robeen,

Perhaps I am making this more complicated than it has to be (as I tend to do):

In the query mentioned in post 5, under criteria i wrote: DMax("[Cut_No]","tbl_dicing_yield, "[Cut_No] = ")

except I don't know how to represent the last part [Cut_no]=?
this should be the max from the blade cuts, which is just an array of numbers (1,2,3...,10).

does this make sense?

[Robeen]

Yes - it is looking very complicated.
Now I really don't know where we are.

First you said:

i want to write some code that will purge [blade_no] of the blades that have reached their maximum cut.

Then you said:

. . . i just wanted to get rid of them in the combo box, to avoid unnecessary clutter for the operators.

Then I said:

If all you want is to prevent the Final Cut blades from displaying in the combo box - then you can change what is displayed in the Combo Box by changing the query in the Row Source [under Data in the Property Sheet].
Select your combo box - click Property Sheet - click Data & then modify the query in the Row Source field.

Are you comfortable writing queries?
Your query for displaying only blade numbers that have not reached Final Cut will say something like this beside 'row Source':
Select blade_no from [YourTableName] where cut_no <> "Final Cut";

At this point I thought you would:
1. Select the Combo Box
2. Go to Property Sheet
3. Click the Data Tab
4. Type in something like this:

 
Select blade_no from [YourTableName] where cut_no <> "Final Cut";

Or

 
Select blade_no from [YourTableName] where cut_no > 9;

Then it got too complicated.



Can we start again?

Tell me as simply as possible ONE thing you need to do.
We can do this one step at at a time!

[mejia.j88]

haha, sure thing, sorry in advance.

I have a form in which employees choose the blade number ([blade_no]) they are going to use, and in a separate field the cut number ([cut_no]) of the blade, there are a maximum of 10 cuts per blade. both of these fields are drop combo boxes.

the blade_no cbo box is editable, as the employees get new blades, they put them into the combo box for later use.

WHAT I WANT TO DO: is to be able to "purge" this combo box from blades that have reached their maximum cut. i have a query called qry_blade_no_order which is the row source for blade_no cbo box. this qry includes blade_no and cut_no.

in the criteria of cut_no, i want a statement that will filter to only show those blades that have not reached a cut count of 10. i.e. i want it to show the blade and its corresponding highest cut.

i have something like this for the criteria:
DMax("[Cut_#]","tbl_dicing_yield, "[Cut_#] =????")

in the question marks, i feel like it should be the set of cuts 1-9. it excludes 10 because i dont want to show those blades that have 10 cuts.

i hope this is more clear.

thanks,
Jorge

[Robeen]

Instead of this:
DMax("[Cut_#]","tbl_dicing_yield, "[Cut_#] =????")
. . . in the Criteria field,

Put this:
< 10
. . . this should give you everything BUT 10.

Let me know if that is what you are looking for.

[mejia.j88]

if i put <"10" it gives me only the blades that have 1.
ideally i'd for it to return the the max per blade.

if i put the stuff indicated above, it gives me a syntax error, trying to use an operand without an operator; and highlights the second [cut_no].

[Robeen]

Try this in your criteria:

In (1,2,3,4,5,6,7,8,9)

[mejia.j88]

mm,
the query isn't returning anything.

[Robeen]

Are the values in that column 'Numeric' . . . or 'Text'?
I should have spotted that you said:
If I put in > "10"
. . . with the 10 in quotes . . .

I mistakenly assumed that Cut 'number' - would be a numeric field.

If they are Text, then try this:
In ("1","2","3","4","5","6","7","8","9")

Let me know if that works.

[mejia.j88]

ok, that worked and excluded the last cut, but it is still giving me all of the values for the corresponding blade number. ex. (blade)B100, cuts: 1, 2, 3, 4, 5, 6, 7.

now to take the max of these values, so that it would only show
B100, cut 7