UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Button To Clear Record, Access 2016    
 
   
geoffk7828
post Sep 13 2019, 11:08 PM
Post#1



Posts: 25
Joined: 21-October 06
From: Barossa Valley South Australia, Australia


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

Attached File  Customer_Complete___2a.zip ( 30.17K )Number of downloads: 7
Go to the top of the page
 
June7
post Sep 13 2019, 11:30 PM
Post#2



Posts: 889
Joined: 25-January 16



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

This post has been edited by June7: Sep 13 2019, 11:30 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
June7
post Sep 14 2019, 01:36 AM
Post#3



Posts: 889
Joined: 25-January 16



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

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


--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
GroverParkGeorge
post Sep 14 2019, 07:48 AM
Post#4


UA Admin
Posts: 35,897
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
geoffk7828
post Sep 14 2019, 09:45 PM
Post#5



Posts: 25
Joined: 21-October 06
From: Barossa Valley South Australia, Australia


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.

Geoff
Go to the top of the page
 
geoffk7828
post Sep 14 2019, 09:56 PM
Post#6



Posts: 25
Joined: 21-October 06
From: Barossa Valley South Australia, Australia


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.

Geoff
Go to the top of the page
 
GroverParkGeorge
post Sep 15 2019, 08:40 AM
Post#7


UA Admin
Posts: 35,897
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
June7
post Sep 15 2019, 01:28 PM
Post#8



Posts: 889
Joined: 25-January 16



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

Review http://allenbrowne.com/ser-61.html
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.

This post has been edited by June7: Sep 15 2019, 01:59 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
GroverParkGeorge
post Sep 15 2019, 01:37 PM
Post#9


UA Admin
Posts: 35,897
Joined: 20-June 02
From: Newcastle, WA


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

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Sep 15 2019, 03:00 PM
Post#10


UA Admin
Posts: 35,897
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
geoffk7828
post Sep 26 2019, 01:23 AM
Post#11



Posts: 25
Joined: 21-October 06
From: Barossa Valley South Australia, Australia


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.

Questions:
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

Attached File  Costomer_Complete_InProgress.zip ( 71.99K )Number of downloads: 7


Go to the top of the page
 
June7
post Sep 26 2019, 07:26 PM
Post#12



Posts: 889
Joined: 25-January 16



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.

This post has been edited by June7: Sep 26 2019, 07:26 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
geoffk7828
post Sep 29 2019, 08:14 PM
Post#13



Posts: 25
Joined: 21-October 06
From: Barossa Valley South Australia, Australia


Hi June7

Thanks for the feedback

Geoff
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd October 2019 - 09:11 AM