Pass a Value on button Click

[davedinger]

i have
A Table named TblPoData
A Table Named TblContactLog


A continous form called FrmPODataList
A Query named QryPoDataList

My problem
On the continious form I need to click on a check box named complete, but becaues it is going through a Query it will not allow me to click on the check box.

My thought is to create a button on each reord of the continous form that will run a macro or code that will send a value to the TblPoData!Complete field to change it to a value of true and then Requery the form

if it is needed the contnious form has a field named ID which is the Record ID of the record on the TblPoData. Maybe that could that be used for a temp value or someting?

Im kind of lost on this one.
Thanks for your help.

[June7]

If you cannot edit "check" (yes/no type?) field via control on form, something is wrong with query and/or form design.

What determines something is "complete"? Perhaps this can be calculated when needed and not saved to table.

Could provide db for analysis. Follow instructions at bottom of my post.

[Micron]

becaues it is going through a Query it will not allow me to click on the check box.

That is not inherently true. See if any of these apply to your query:
http://allenbrowne.com/ser-61.html

[davedinger]

Database is attacheed
the way this should work is on open you will see a menu screen click on the unmatche invoics button
this will take you to the po data list continious form.
on this form you have 2 options.
1) to indicate all of the work needed is complted on a PO you should be able to click on the rec Completed check mark (The Green column)
that will trigger it not to show up the following day.

2) If More work is still needed on a PO click on the PO # for example 1025421. this will bring up a subform wiht varirious statuse adn fields. you can make changes then hit close and the changee will show up on the PO data List continious form.
all of that works good

Some Po's are imported and do not need to have anything done to them so What i am trying to do is make it so you do not need to go to the subform just to indicate the record is completly.

[June7]

First of all, in PoData the ID field is designated as primary key yet you link on P/O Number field. Designated primary key should be saved as foreign key in related table. If you want to use the text P/O Number as key then designate it as such and the autonumber ID continues to serve no purpose. Then the checkbox will be editable on PoDataList form.

Second, you are using punctuation/special characters and spaces in naming convention. This is a bad design. It can be dealt with but will be annoying. All upper case is also not advised just because it is harder to read. Better would be PartNumber or Part_Number - underscore is an exception.

Next, a form would normally do data entry/edit for one table. If you don't do as instructed above to change the primary key, remove ContactLog from PODataListOpen RecordSource and the check box is editable. If you want to edit both PoData and ContactLog records, use a form/subform arrangement as you have done with PoDataDetailOld. However, don't understand why you have compound Master/Child links on this form/subform.

Again, what determines that a PO is complete - when all related ContactLog records are complete? If so the PO complete can be calculated instead of relying on user to think about this data edit and running risk of it getting 'out of sync' with data.

Having a yes/no Complete field as well as a date Complete field is redundant - as in ContactLog. If there is a date value then it is complete. The yes/no is not needed.

[davedinger]

Sorry I couldn’t reply for so long, I had a family emergency and screen time was limited.

To address all of the issues you brought up I’m sure you are right, but I’m not a Professional Developer, most of the database things I make are for jut my use, and I’m self-taught by making things like this, so although they may be sloppy most of the time I am just making them up on the fly to make some task easier. I guess you can think of what I do as back yard programming just for my purposes.

So let me explain what this database is for. Daily I receive a list of purchase orders that our company received the day before. I match those up with invoices that our vendors send. some are perfect matches and I "'complete" them right away. Others have issues and I use the database to keep track of the ones that have issues until all issues are solved then I mark them complete.

So, the way I have this database working is that every morning run an import macro that runs a series of queries. it starts with 1deleteImportedRawData. this delete all of the data from the previous day that is in a table named imported raw data. Step 2 Then imports the new data from a spreadsheet into the table ImportedRawData. So now we have today’s data. I then I use an update query 3modifydataToRawData Before Moving... this adds an import date to the raw data table. step 4 named 4MoveNewRecordsToPOData does what it says it takes the data from the raw data table and moves only the new records imported today to the POData table. I know this doesn’t allow a history of the records but I do not want or need a history. I use the data and then when it is done "Completed" the next day it is deleted. I guess I’m using the database more as a check list than a data resource. I know there are better ways to do this but this works for what I need now.

So now let’s look at how this is all used.

Every morning after the update procedure I open the form PODataListOpen. This is basically my main status page of all of the new and previous purchase orders. it shows all of the records in then POData table and their current status. some are records from today’s import and others are records from previous days that have notes and are not completed yet. They also provide data for other indicators to help me know the status of the Purchase order. like if I have contacted the store, or if there has been a discrepancy form filled out etc.


This the part I’m trying to solve.

My first step daily is to remove easy Purchase Orders. those are the purchase orders that the amounts match up and there is nothing to do but complete them. I look at the invoices on the list and the invoice that I received from our vendors and match them up with what we received. I would like a way to mark those complete from the PODataListOpen form with a simple click but I can’t because in order to get some of the other data displayed on the form like if discrepancy forms have been completed and if notes to the stores have been sent, etc. I have to use a query for the forms data source. that means I can’t get a check box on the form.

Now as far working with the other records on the form that have issues and need notes, all I have to do is click on the PO number data field and it will open a sub form that will allow me to update everything Including the Complete field but this is a really slow way to update that field. I would like to find a way to mark them complete without having to go to the subforums. When I’m done updating a record I close the form and it re-queries.

My thought is that since The PODatalistOpenFrom won’t allow me to click on a check box due to its coming from a query that maybe there is a way to put a button on the form for each record that would send a value of true to the field named ReceiverComplete in the POData table, then execute a re-query on the form to show the new status.

Dose that make any sense?

I know its meatball programming but I’m still learning by the process of need.

Thanks for your help

[June7]

Until you fix the PK/FK issue I identified, there is no reason for me to conduct further analysis of this db. Read first paragraph of my previous post again.

[davedinger]

So I got creative on this one, i couldn't update the record completed check box because it was on a continuous form resulting from a query.

So, I thought out side of the box.
1) I looked at my PO data table and realized each of the records had a auto record ID field. so i created a small form that had the fields of the Po Record ID and the check box to indicate complete.
2) then I set the on open event on the form to automatically set the check mark to true.
3) Then on the on on the continuous form i simply made a button to run a macro to open the form where the Record Id's were =, then close the form and re-query the Continuous form.
bada boom the magic happens.

I'm sure this is breaking a thousand good programming practices, but it works.