Enter "None" for Item IDs 1-100 1

[infinitx]

Hi,

I need to have a command button that adds the word "None" into the ICL field in a table.

Here's the table structure:

Item ID (1-12000)
ICL (Text)

For Item IDs 1-100, I want the button to add the word "None" to the ICL Field Name that corresponds to those Item IDs

Is this possible?


Thanks a lot

----
Alex

Anytime things appear to be going better, you have overlooked something.

 

[glalsop]

Sounds like you need an update query, something like:

UPDATE Table1 SET Table1.ICL = "None"
WHERE Table1.ItemID <= 100;

Probably best to test on a copy of the original table.

Good luck

-Gary

 

[infinitx]

Thanks!

That is exactly what I needed!

One more question, if the user is entering an ICL that corresponds to, say Item ID #10. After they click done, how could the query change accordingly so that it adds "None" to ICL Field Names that correspond to Item IDs 11-400?

Thanks again!

----
Alex

Anytime things appear to be going better, you have overlooked something.

 

[glalsop]

Where are you going to get the value 400 from?

If you need to do this repeatedly and dynamically, which is what I think you're getting at, probably easiest to just execute an SQL command from the Click event of your button, and reference values on your form.

For instance, to set the ICL field for all records that have an ItemID greater than the current record on the form to "None", you could say:

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE Table1 SET Table1.ICL = 'None' WHERE [Table1].[ItemID] > " & Me.txtItemID
DoCmd.SetWarnings True

Something like that.

Good luck


-Gary

 

[infinitx]

Gary,

Sorry about the 400, I meant to say 100.

Thanks! The code worked!

One more question though. I want to add "None" to ICL Field Names that have an Item ID between say the Item ID of the record that the user is viewing and 65 (which is the number located in txtNumber)

I tried something like this:

DoCmd.RunSQL "UPDATE Table1 SET Table1.ICL = 'None' WHERE [Table1].[ItemID] <= " & Me.ItemID And WHERE [ICL 3].[ItemID] >= " & Me.Text38"

I get the following error:

"Microsoft Access can't find the field "|" referred to in your expression"

What am I doing wrong?

Thanks a lot!

----
Alex

Anytime things appear to be going better, you have overlooked something.

 

[infinitx]

Sorry,

I meant to say:

DoCmd.RunSQL "UPDATE Table1 SET Table1.ICL = 'None' WHERE [Table1].[ItemID] <= " & Me.ItemID And WHERE [ICL 3].[ItemID] >= " & [b]Me.txtNumber[/b]"

----
Alex

Anytime things appear to be going better, you have overlooked something.

 

[glalsop]

Try this:

DoCmd.RunSQL "UPDATE [ICL 3] SET [ICL 3].ICL = 'None' WHERE [ICL 3].[ItemID] <= " & Me.ItemID & " and [ICL 3].[ItemID] >= " & Me.txtNumber

You just need to move the form references outside of the quotes, since we are concatenating them to the literal string value that we want to pass to the DoCmd.RunSQL method.

Good luck



-Gary

 

[infinitx]

Yes, it worked perfectly!

Thank you!

----
Alex

Anytime things appear to be going better, you have overlooked something.