472,780 Members | 1,717 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,780 software developers and data experts.

How to create a data entry form (using a query, too) that appends to a table.

Good morning!

I looked through my Access bible but I'm baffled.

I have a table and it was made into a query and then used in a form.

The end user enters the information (and/or chooses from drop down boxes). There are four entries to make and from those entries they automatically create a handful of various symbols. (an example of one symbol format: SPX 090221C00221000).

What I need to do is for every entry made and symbols created, they all need to write to a table, the same table (like a tally - it continues to grow).

Can I do this? I'm trying to figure out how to use the Append from the query by form I made.

If more information is need please let me know. I'm not sure how to ask the question and if I'm leaving anything major out!

Thank you for the help!
Nov 11 '10 #1
14 4973
32,534 Expert Mod 16PB
Typically, you bind your dataset to the form (or more accurately vice-versa) and the data can then be updated or even added to that way.

I'm not sure if this is what you're thinking of (it should be) or if you're thinking of appending data using SQL after the fact. Suffice to say appending data on a record by record basis using SQL is rarely advisable. I say rarely, but only because I accept the idea that it's possible there's a scenario I don't know of where it may be appropriate. I certainly can't think of one though.

Clearly, any query bound to in this way would need to be an updatable query (See Reasons for a Query to be Non-Updatable).
Nov 11 '10 #2
Hi NeoPa,

I'm clueless, please forgive me. There is probably a very easy solultion to what I need to do but haven't figured it out yet. I'm greatful for Bytes!

But I'm going to fiddle some more and then come back to Bytes if need be.

After I sent the message I realized I may know how to do this. But it's doubtful.

Thank you!
Nov 11 '10 #3
32,534 Expert Mod 16PB
Let us know if you continue to struggle Faith. Be as clear as you can about what confuses you and we'll try to help with your specific problem.
Nov 12 '10 #4
Thank you NeoPa!

Yes, learning how to put in words exactly what I'm trying to accomplish w/o over kill.

Basically this is what I have:

I have a table (Security Master), a form (Make Symbols), and a query (MakeSymbolsQry).

The form asks the user for four things to be entered. Three are controls one is a text box. The controls are drop down boxes where the user would choose from. The text box is where the user would enter a quantity.

these four things create 6 different alpha-numeric symbols. I created a query that takes that data and converts said data into those 6 differnt symbols. Learned that here!

Now what I have to do is have that query dump the symbols it generates into a table. That table should house the combo box choices, the text box qty entry, and the six alpha-numeric symbols created.

That is where I'm running into a problem. How do I get the query (alpha-numeric symbols) to write to that table?

Nov 12 '10 #5
32,534 Expert Mod 16PB
Two most fundamental questions :
  1. Is your query currently updatable?
  2. What is the SQL of your query now?
Nov 12 '10 #6
1. I don't know (**blushing from embarassment**). How do I find out?

2. See SQL below...

Expand|Select|Wrap|Line Numbers
  1. SELECT [RecID]
  2.      , [DOE]
  3.      , [Expiration]
  4.      , [Strike]
  5.      , [Option]
  6.      , [Underlyer]
  7.      , [Mellon]
  8.      , [Underlyer] & " " & Format([Expiration],"m/yy") & " " & [Option] & [Strike] AS BBS
  9.      , [Underlyer] & " US " & Format([Expiration],"mm/dd/yy") & " " & [Option] & [Strike] AS BBL
  10.      , [Underlyer] & "   " & Format([Expiration],"yymmdd") & [Option] & Format([Strike],"00000") & "000" AS OCC
  11.      , [Underlyer] & "  " & Format([Expiration],"yymmdd") & [Option] & Format([Strike],"00000") & "000" AS UBS
  12.      , [Underlyer] & "  " & Format([Expiration],"yymmdd") & [Option] & Format([Strike],"00000") & "000" AS NE1
  13.      , UCase([Underlyer] & Format([Expiration],"mmm") & [Strike] & [Option] & Format([Expiration],"yyyy")) AS NE2
  15. FROM [Security Master];
Nov 12 '10 #7
332 100+
I think this might be a case to be handled by an unbound form. The Save button would trigger a function that:
would read the content of the controls,
format the data to be inserted the "alphanumberic way" then exscute a sql statement to do the insert.
Nov 12 '10 #8
Thank you Mariostg!

I don't know what you mean when you say:

"format the data to be inserted the "alphanumberic way" then exscute a sql statement to do the insert. "
Nov 12 '10 #9
32,534 Expert Mod 16PB
To determine if a query is updatable or not, simply open the query and if the last visible line is empty and marked with an asterisk (*), then it is updatable. Non-updatable queries show only the current data, without any line at the bottom for a new record.

As far as using unbound forms goes, this is not something I would ever recommend except where there is no possible way to approach it using bound forms. I'm pretty sure we're not at that stage yet. It's a more clumsy approach that leaves you doing more work in the long run, as it bypasses what Access does for you, rather than utilising its features. There's no question it can be made to work, but always at a cost to the design of the database.

PS. I'll look at your SQL when I have reformatted it somewhat.
Nov 12 '10 #10
32,534 Expert Mod 16PB
What types of controls (on the form) are handled by :
  1. BBS?
  2. BBL?
  3. OCC?
  4. UBS?
  5. NE1?
  6. NE2?
Nov 12 '10 #11
The controls (if I'm using the word correctly) are:

Expiration Date
(these are set dates that are used in a combo box)

(would be P or C which means Put or Call - asset management term)

(an ancronym used and currently we only use SPX but later down the road will use others.)

(this is the quantity of the put or call)

Now, those four things together are what make an alpha-numeric symbol for each of those (6) things you noted (we'll call them "houses").

Each of the houses have a specific format we must follow.

Here's a for instance: (I'm using old data to test it, fyi)

Expiration date: 07/19/2009
(this is a date chosen by the user from a drop down box)

Option: C
(user would chose a C or a P from a drop down box)

Underlyer: SPX
(SPX is default right now but there will be more and is in a drop down box)

Strike: 718
(a text box where the user enters the quantity of the Option)

What's entered above would then create the following symbols (which I utilize the query to do it):

1. BBS - SPX 7/09 C718
2. BBL - SPX US 07/19/09 C718
3. OCC - SPX 090719C00718000
4. UBS - SPX 090719C00718000
5. NE1 - SPX 090719C00718000
6. NE2 - SPXJUL718C2009
7. Mellon (which I do not have in the query for making a symbol because it must be mannually entered because they have no set format)

Each of those houses want their symbol a certain way.

As far as controls, not sure what you mean. I have a table with those listed in it and they are text boxes waiting to be filled with a symbol.

How are my explanations - better yet?

Thank you so much for the assistance!
Nov 12 '10 #12
And I'm wondering if I should make tables out of the combo boxes? Have the stuff save in the table and then run that query to make the symbols. but how do I make the symbols stay? I am supposed to keep history. Know what I mean?
Nov 12 '10 #13
ok, I think I know what I should do.

Make a form out of a table that has those four elements. Everything will be saved. When someone needs a report, use the query...and I just found out I can export the query to Excel!

Thank you everyone for helping the brain to work.
Nov 12 '10 #14
32,534 Expert Mod 16PB
Controls are the items on your form that manage and show data (typically). TextBoxes; ComboBoxes; ListBoxes; etc are all controls.

It seems obvious that the data in the calculated fields of the query does (absolutely) not need to be stored anywhere at all. The query will (almost certainly) not be updatable with these calculations in it, but if these calculations were performed on the form itself in the various (unbound) controls, then they could be updated on the fly as data were added or amended. Normalised data (See Database Normalisation and Table structures) never includes the same data saved in more than one place (except exclusively for the index information).

Does that make sense?
Nov 12 '10 #15

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Iain Miller | last post by:
Struggling a bit here & would be grateful for any help. I have a table which has a list of people in it. Each person has a unique ID automatically allocated by Access but also belongs to one of 5...
by: Alex Wisnoski | last post by:
Access 97SR2-I am trying to create a Job Position data entry form based on a table. The form has 15 fields on it. I want to use a combo box to look in the table and see if the position number...
by: intl04 | last post by:
Is it possible to create a Word form as the data entry form for an Access database? I didn't see any reference to this possibility in my Access books, so I'm sorry if this is a question that is...
by: Alex.Wisnoski | last post by:
I have a data entry form with a combo box to look up an entrant's name. If the name is already in the table then it pulls up the record and that part of the form works fine. If the name isn't in...
by: KC | last post by:
Hello, I am using Access 2002. WinXP, Template from MS called Orders Mgmt DB. I have tweaked this DB to work for our small co. It has worked pretty well up until I made the mistake of deleting...
by: Aspnot | last post by:
Background: I have a data entry form that is bound to a DataSet. This DataSet contains 9 tables and the form displays data from each table in textboxes, not a DataGrid. One of the tables in the...
by: filbennett | last post by:
Hi Everyone, I'm generally unfamiliar with Access form design, but have programmed Cold Fusion applications for a couple of years. I'd like to build a data entry form in Access that allows the...
by: pw | last post by:
I'm an experienced Access/VBA programmer, trying to become competent using VB.net. Using base vb.net it seems to me to be an AWFUL amount of work to create data-entry forms similar to what one...
by: Gino Perruti | last post by:
I am converting an old ASP data entry form to ASP.NET 1.1. The old form has a script block that dynamically adds rows to a table that contain cells with a textbox for quantity, product...
by: JohnnyMid | last post by:
I am working on a database, and have been having some trouble with the auto number feature. Occasionally, the data entry form is saving blank records into the table. I am trying to figure out a way...
by: Rina0 | last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: lllomh | last post by:
How does React native implement an English player?
by: Mushico | last post by:
How to calculate date of retirement from date of birth
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.