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

[Fspinelli]

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!

[NeoPa]

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).

[Fspinelli]

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!
Faith

[NeoPa]

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.

[Fspinelli]

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?

TGIF!

[NeoPa]

Two most fundamental questions :

1. Is your query currently updatable?
2. What is the SQL of your query now?

[Fspinelli]

1. I don't know (**blushing from embarassment**) . How do I find out?

2. See SQL below...

SELECT [RecID]
     , [DOE]
     , [Expiration]
     , [Strike]
     , [Option]
     , [Underlyer]
     , [Mellon]
     , [Underlyer] & " " & Format([Expiration],"m/yy") & " " & [Option] & [Strike] AS BBS
     , [Underlyer] & " US " & Format([Expiration],"mm/dd/yy") & " " & [Option] & [Strike] AS BBL
     , [Underlyer] & "   " & Format([Expiration],"yymmdd") & [Option] & Format([Strike],"00000") & "000" AS OCC
     , [Underlyer] & "  " & Format([Expiration],"yymmdd") & [Option] & Format([Strike],"00000") & "000" AS UBS
     , [Underlyer] & "  " & Format([Expiration],"yymmdd") & [Option] & Format([Strike],"00000") & "000" AS NE1
     , UCase([Underlyer] & Format([Expiration],"mmm") & [Strike] & [Option] & Format([Expiration],"yyyy")) AS NE2

FROM [Security Master];

[Mariostg]

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 "alphanumbe ric way" then exscute a sql statement to do the insert.

[Fspinelli]

Thank you Mariostg!

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

"format the data to be inserted the "alphanumbe ric way" then exscute a sql statement to do the insert. "

[NeoPa]

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.

[NeoPa]

What types of controls (on the form) are handled by :

1. BBS?
2. BBL?
3. OCC?
4. UBS?
5. NE1?
6. NE2?

[Fspinelli]

The controls (if I'm using the word correctly) are:

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

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

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

Strike
(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!

[Fspinelli]

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?

[Fspinelli]

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.

[NeoPa]

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?