Yes/No Field updating a table that isnt in the record source

[coffeeman]

I have a material Receiving Material Table that gets updated based on entries when material is received in each having a specific lot code number.

I also have a Daily Production Form that I enter in a customer's materials used. As of now, when a customer's material is used up, I go to the Receiving Log table and click on a yes/no checkbox and select that is a "Completed Order" so I know to use the next lot code of the customer for the material that is now used up.

Is there a way to create a yes/no checkbox in the form that I can click on to automatically update the material and its lot code as "Completed Order" in the Receiving Material Table.

By the way, the form is bound to a table called "tblDailyProduction".

THANKS!

 

[Banana]

One way to do this would be to use an Update query and call it on the checkbox's AfterUpdate. You would probably want to create a parameter query to pass the Customer's primary key and the value of the checkbox. You also can write ad hoc SQL in VBA, but I would think parameter query is more proper way to do it.

 

[coffeeman]

Not very familiar with parameter query. Do you mean set a query up put something in the criteria? Will this be something the operator must enter?

Is there an example of what i can do?

Thanks a bunch!

 

[Instance]

I have a material Receiving Material Table that gets updated based on entries when material is received in each having a specific lot code number.

I also have a Daily Production Form that I enter in a customer's materials used. As of now, when a customer's material is used up, I go to the Receiving Log table and click on a yes/no checkbox and select that is a "Completed Order" so I know to use the next lot code of the customer for the material that is now used up.

Is there a way to create a yes/no checkbox in the form that I can click on to automatically update the material and its lot code as "Completed Order" in the Receiving Material Table.

By the way, the form is bound to a table called "tblDailyProduction".

THANKS!

I think the best way would be to link the tables in the Form's data source so you have the field available on the form. If you don't understand Left/Right/Inner joins, you should definitely look them up. Not terribly complex, but super powerful.

Alternatively, you could do it with a little VBA code. This is tougher to debug and track, and I normally woudln't suggest it. But, if you're looking for an alternative and for some reason a join wont work, you can give it a try. The OnClick() method of the checkbox could run an update query based on it's own value. Something like:

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tMyTable SET tMyTable.SomeField = " & chkMyCheckbox & " WHERE (tMyTable.PrimaryKeyField=" & Me.PrimaryKeyField & ");
DoCmd.SetWarnings True

This would run completely unrelated to the current form's recordset, and change the value in the tMyTable table rather than whatever is currently in memory.

I would strongly encourage linking the tables in the form's data source, however. It will be a far more predictable, pain-free solution than working in code -- especially if you don't know VBA well.

 

[coffeeman]

Ok. I think I get where you are coming from. Is there a way to join them without invoking an SQL query. Right now the Record Source says "tblDailyProduction". I never had a query for it. If I go to change the Record Source, it invokes the SQL.

I have roughly 35 fields that get entered into this table.

Thanks again!

 

[coffeeman]

Ok, so I got the UPDATE query to work!

Everything is working so far. I have another thing I would like it to do.

The way my form is set up is:

When I select a Coffee, a Coffee Lot Code is updated auto based on the coffee selected in the form and the CurrentLotCode Yes field in my Receiving table.

This is the form

Coffee: SampleA

Cofffee Lot Code: 123456 (autofill based on selection of coffee and what the currentlotcode is selected in the receiving table)

Completed Lot Code (Click command button - that runs the update query to update the two tables)

When the update query is ran it changes the fields in my Receiving table to Completed Lot Code to YES and the CurrentLotCode field to NO.

How can I make the table go to the next available lot code of the selected and now update coffee.

For Example:

Receiving Table

ReceivedDate Coffee CoffeeLotCode CurrentLotCode CompletedLotCode
12/12/08 SampleA 654657 No No
11/11/08 SampleA 222222 No No
10/10/08 SampleA 123456 Yes No

After Update Query is ran I would like it to do this for me:

ReceivedDate Coffee CoffeeLotCode CurrentLotCode CompletedLotCode
12/12/08 SampleA 654657 No No
11/11/08 SampleA 222222 Yes** No
10/10/08 SampleA 123456 No* Yes*

*Already have the update query performing this after update.
**Would like the query to select the next available by date received lot code for CurrentLotCode

Also, if there is no selection to be made (meaning no new lot codes are left), will it continue without making change?
Can I make it so when a new one does eventually get entered, the CurrentLotCode will be selected?

I have to be careful with that because, as you can see from above, the newest lot code does not need to be selected as CurrentLotCode when it gets received in. Need to be able to do First in, First Out.

Thanks for the Help! and sorry if lengthy!

 

[coffeeman]

Would it help if since I am already updating and have updated all completed lot codes, that maybe instead of having it select a current lot code, that I should have the minimum date received as the selection for the Coffee Lot Code?

This way when I update a coffee lot code, it then will select the currentlotcode as the date minimum received based on lot codes that are not completed. Does this make sense?

Any help on coding for "using the date min received" would be appreciated.

THANKS!