January 11, 2021 at 5:25 am
Hello Everyone - I am a newbie to SQL scripting so please don't take anything for granted regarding my knowledge of SQL, bearing in mind that I have had very limited exposure to it. That been said let me explain my challenge as best I can.
I am developing an application using a RAD tool that utilizes SQL queries when needed. I am using a MS Access database with several tables, the table in question here is "points". My app being developed is to be able to capture data relating to the results of various sports events. I have a form with the following fields on it which the user has to populate, regarding the sporting events.
Sport_type (Dropdown listing various sports types)
Venue (Dropdown)
Event_date
Numberof_competitors
Nameof_Competitor
Class_Comp (Seniors, Juniors, Ladies...)
Point_achieved
......and more
Keeping in mind that a sports event being captured on this form can have large numbers of competitors for which results have to be captured it becomes a tedious exercise to repeatedly populate the the first four fields with the same information when adding new records. What I need is to "keep" the values of the first four fields when adding new records (for each competitor) so the user does not have to continuously complete them. I can add a SQL script to the "New" button to populate those four fields based on the value of the previously committed record - if so, what would the script look like? At present the "New" button simply appends a new empty record to the table and the user is presented with blank fields on the form, ready for completion.
I could also add the script to the "Save" button of the form if it will be more practical to perhaps save the captured values of the four fields into variables to be able to populate the new record's fields with values from these variables?
Any assistance will be appreciated, thanks in advance.
January 11, 2021 at 9:23 am
Welcome, but please note that this is a SQL Server forum, not an MS Access forum.
Having said that, if you are doing this coding totally within Access, it should be fairly straightforward to solve this problem using VBA and Access forms, rather than SQL.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 11, 2021 at 9:48 am
Thanks Phil, but no I am not using MS Access, just an Access database The RAD tool I am using can accommodate several different different database types (including SQL Server) but the only scripting it accommodates is SQL.
January 11, 2021 at 6:03 pm
It will be difficult for anyone here to help you, based on this limited amount of information & the fact that we have no knowledge of what this RAD tool is or how it works.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 11, 2021 at 8:04 pm
This appears to be a design issue - not really a coding issue. The design should be modeled on a header/detail type model - where you have a table (and part of the form) for the header data (the first 4 fields) - and a detail section with a row for each competitor.
There is no reason for the field 'Numberof_competitors' - that is calculated by the number of rows in the detail section (and a separate competitor_details) table.
In Access - if I remember correctly (it has been many, many, many years since I used it) your form would have a table type control tied to your details table and related to the outer form which would be tied to the header table - and related on the identifier used for the header table.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply