Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Tool bars, Menu bars + Ribbon _ Button To Clear Record

Posted by: geoffk7828 Sep 13 2019, 11:08 PM

Hi All,

The last step to complete this part of my project (attached below) is to have a button to accept the payment, I have been playing with this over the last few days and have had no success.

The purpose of the Form "Payments_Subform is to highlight any "Unmatched Payment" then decide whether to accept or reject the payment, if I accept the payment the record is cleared from the Form, if I reject the payment the record is to remain in the Form.

I need to use the button to clear the record from the Form, I have tried using an "UpDate Query" to clear the "Unmatched" from the Field which resulted in clearing the column, I have also tried "RecordSet".

Can some point me in the right directioinm.

Geoff ( 30.17K ): 7

Posted by: June7 Sep 13 2019, 11:30 PM

You are using a bound form. If you want to accept data and 'clear', just move to new record row.

Posted by: June7 Sep 14 2019, 01:36 AM

Why are you binding form to a non-editable query?

You are not linking tables on primary/foreign key fields.

Posted by: GroverParkGeorge Sep 14 2019, 07:48 AM

In the interest of appropriate responses, please let us know. Are these tables the ONLY tables in your working database? Are the tables in your working database set up the same way? Did you deliberately omit Relationships for these tables for this sample db?

We need to know whether to address design problems in the tables before we can talk about using them in forms.

Thanks for clarifying.

Posted by: geoffk7828 Sep 14 2019, 09:45 PM

Hi June,

Thanks for your reply

Please forgive my inexperience, but why is my query non-editable.

You are not linking tables on primary/foreign key fields, could you please be more specific.

I have been building and modifying my DB for many years, learning on the fly getting information from researching and from Forums, I am now trying to be more formal in my approach.


Posted by: geoffk7828 Sep 14 2019, 09:56 PM

Hi George,

Thanks for your response.

There are some 15 Tables in the main DB, yes they are all similar in setup, the relationships were an oversight when I did the sample DB.

As I indicated to June7, I have been building my DB and learning on the fly solving or working around problems as they arose.


Posted by: GroverParkGeorge Sep 15 2019, 08:40 AM

Unfortunately, then, the table structures probably should be revised before worrying about an interface.

I've observed over many years here at UA that many, many "interface" or "query" problems derive from inappropriate table designs. Compensating for those problems in an interface is possible, but usually involves more work.

Let's see if we can sort out the tables a bit.

Posted by: June7 Sep 15 2019, 01:28 PM

By 'non-editable' I mean cannot change value in any field.

See 8th reason.

In Payments you have PaymentsID designated as primary key yet you save JobNo into Customer_Completed - (or are you saving Customer_Completed JobNo instead of ID into Payments?). Is this relationship supposed to be one-to-one?

In Customer_Complete_JobOutcome you have ID designated as primary key yet you save Job_Outcome into Customer_Completed.

Normally a form should do data entry/edit for only one table. Why are you including all 3 tables in this form RecordSource? It is possible to include multiple tables in a form RecordSource but really need better understanding of relationships and goal to advise.

Posted by: GroverParkGeorge Sep 15 2019, 01:37 PM

Two of the problems I noted and wanted to clarify before investing work in addressing on the sample tables.

Posted by: GroverParkGeorge Sep 15 2019, 03:00 PM

As I dug into this, parts of it started to look familiar so I went back and looked at previous sample accdbs I'd worked on. Sure enough, I found it in August.

Apparently, we're not working with the original tables in this "sample" and that makes it really hard to offer anything useful in regards to specific questions about the queries involved.

Sorry, but I have to ask for a CURRENT version of the accdb instead. That way we can be more confident we're offering relevant information.

Thanks for giving us up-to-date, accurate information to work with as we try to help you solve the problem.

Posted by: geoffk7828 Sep 26 2019, 01:23 AM

Hi George and June7

Thanks for your reply, I have been referencing to the documents mentioned in your post Aug 14 2019, 01:35 PM and trying to implement suggestions from June7 and yourself, it has been almost 10 years since I finished compiling my original DB and have forgotten more than I thought.

To explain:

I contract to a company completing installations and service calls, which are paid on a job by job basis, on completion of a job I receive an e-mail with relevant information, this is in column format, I copy and paste this data into Excel and manipulate the data into rows, which is then saved in an Excel file "Completed Import", I then add the date in 'column A", any notes in 'column B" other Job info in "column C" the file is then imported into my Access DB to Table "Completed_Import" then appended into "Completed_Jobs", this is why this table is not normalized.

The results that I require is to be able to have a Form show job details and payments, highlighting "PaymentDue" and "Payment" ("PaymentDue <>Payment"), another form to show jobs not yet invoiced, generate some reports and search for "Job_No's, stock items etc.

1. Should I break down the "Completed_Jobs" table to completely, create a more normalized structure.
2. Remove the stock items and create separate tables.

Any suggestions would be welcomed.

Geoff ( 71.99K ): 10

Posted by: June7 Sep 26 2019, 07:26 PM

Might want to rename database with correct spelling of "customer".

Not seeing any need to break up Completed_Jobs. Each record is unique, Job_ID is not repeated.

You are saving Job_Outcome text into Completed_Jobs table instead of key value.
You are saving Job_No into text field of Payments table instead of saving CompletedJobsID key.
So again, linking is not on designated key fields.

You are saving numeric Payment value into a text field.

Posted by: geoffk7828 Sep 29 2019, 08:14 PM

Hi June7

Thanks for the feedback