One Form to Add Multiple records to one table

[braven]

Hi All

Probably as you noticed it is my first post on this forum, so also probably you have already figure it out that I'm sort of beginner in Access.
That is Why I'm asking for help. If any of you know the answer or is aware of existing thread with an answer please help.

Now a little bit about what I'm trying to do.

I have two tables in database; Route and TicketPrices.





Now, I'm trying to add records to TicketPrices table through a form also called TicketPrices. However to save time and simplify future inputs I would like instead of adding one record per day per route.
So when I open this form, I would like it to prepare inputs for all/some routes existing in routes in Route table in rows and in columns prepare places to enter data not only for one date but for a whole week.
There are also some fields which should be common for all records. So basically each ticket price should create a seperate record with price, route, ticket date and input date/time.

I have layout for this form, but have no clue how to make it to work.



Could you tell me is it possible to do?
And if so, how to do it?

All help will be veeeery appreciated.


System: Win 8.1
Office: Access 2010

Hope to have a quick replies ;-)

[orange]

Can you describe in plain English what you are trying to do? Just as you would tell someone who doesn't know you; doesn't know access/database.
I'm not following your current post.

[June7]

This is obviously an UNBOUND mockup form. It is not a practical arrangement for data entry into the table. Using UNBOUND form to save data will require a LOT of VBA code.

What is fairly simple and easy is a BOUND form that lists the records continuously just as they are in the table. Code can be used to set DefaultValue property of controls where you want values to carry forward to next record. After entering values for first record, all you have to do is enter the price on each new row and the other data is filled in.

http://access.mvps.org/access/forms/frm0012.htm

Another approach with a bit more code can create daily records for whatever period and route(s) you want, present that set of new records on form for edit and all you have to do is change the price where necessary.

[braven]

In simple words, I would like to have form which will get all routes from Route table, then will allow me to put ticket price of route for each of dates and then save those data to ticket price table.

Build of both tables is shown in opening post.

[June7]

As described in my previous post, this is possible but requires code.

TicketPrices table appears to have Lookup field for the RouteID. Advise not to build lookups in table.

[braven]

@June7
Sorry, my previous post was to orange, with explanation he asked for, I send it before I saw your reply ;-)

I'm not afraid of VBA, I know it quite well from Excel, so in Access I would probably manage to do it as well and I think I know how I could add data to table. I just would have to loop through all "rows" and "columns" on form, this should be fairly easy.
Here my only concern would be how to get route id's listed on a form for later use while adding new records. That is regarding UNBOUD solution.

I'm not quite getting how to do it with bounded form.
If I understand correctly, then each time for new record I would have to choose next route id; ticket date and input date would carryover (using code)?
Then I would have to repeat whole operation for each of days? That solution is the simplest but very time consuming during data input. However if I won't find any other way that is what I will have to do ;-)

Another approach with a bit more code can create daily records for whatever period and route(s) you want, present that set of new records on form for edit and all you have to do is change the price where necessary.

Correct me if I'm wrong, but you suggesting to create all records with VBA first and then pull them in to form for editing? Would this allow me to get something similar to layout I have presented in form screenshot from first post?

TicketPrices table appears to have Lookup field for the RouteID. Advise not to build lookups in table.

Yes, there are some lookup in tables, but I can move them to form which will be used to add new routes to table.


I would like to use bulk adding of records to save some time at input, as it might be quite often rather than adding ticket price one by one each time choosing also route. That is why I came out with proposed layout.
I know it will take only a second or two each time to pick route, but if you have to do it hundred times then it's getting not to efficient ;-)

[June7]

Presenting existing data of normalized structure in the unbound form arrangement you want would require a LOT of code and then code to save edits.

[braven]

Thank you for all help so far.

I have used form and sub form to sort out the problem. In form I'm going through routs and in sub form I'm adding one by one dates an prices.
It's not an ideal solution but it works so far.

Access is still unknown territory for me but I will get there.

In a mean time could I ask for help with one more thing.
Going with your advise I hale removed lookups from the table and also I have added additional table with list of all cities which is used to add be routes. But now I have problem with showing in form names of cities instead of their codes.
After I added relationship between tables. Only one city name is pulled from cities table to route form both route start and finish point.
I have manage to overcome this problem using query and manually editing SQL statement, but is there a better way of doing it? Using access it self without need of editing SQL statement?

[June7]

If the Route table has ID code for city instead of the actual city name, query needs the City table pulled in twice. The result will be two copies of City table in the query. One each joined to each of the fields in Route table. Is that what you did with manual edit? Try that in the relationships builder as well.

[braven]

Hi

Sorry for late reply.

Original SQL statement after creating it in Access looked like this, with relation set only between City.CityID and Route.CitySID

SELECT Route.RouteID, Route.OperatorID, Operator.OperatorName, Route.CitySID, City.StopCity, Route.CityEID, Route.NrPolaczenia,
Route.LataPn, Route.OdlotPn, Route.PrzylotPn,
Route.LataWt, Route.OdlotWt, Route.PrzylotWt,
Route.LataSr, Route.OdlotSr, Route.PrzylotSr,
Route.LataCz, Route.OdlotCz, Route.PrzylotCz,
Route.LataPt, Route.OdlotPt, Route.PrzylotPt,
Route.LataSo, Route.OdlotSo, Route.PrzylotSo,
Route.LataNd, Route.OdlotNd, Route.PrzylotNd,
Route.LastUpdate
FROM Ticket, City INNER JOIN (Operator INNER JOIN Route ON Operator.OperatorID = Route.OperatorID) ON (City.StopID = Route.CitySID) AND (City.StopID = Route.CitySID);

I have tried it also a with relationship set (City.CityID and Route.CitySID) and second one to (City.CityID and Route.CityEID),
what created a duplicate City table in Relationship view, but query was displaying an error and didn't show View view at all.


Then I have changed SQL statement manually to this one below.

SELECT Route.RouteID, Route.OperatorID, Operator.OperatorName,

Route.CitySID,
(SELECT StopCity FROM City WHERE City.StopID = Route.CitySID) AS CityFrom, (SELECT StopCountryCode FROM City WHERE City.StopID = Route.CitySID) AS CountryFrom,

Route.CityEID,
(SELECT StopCity FROM City WHERE City.StopID = Route.CityEID) AS CityTo, (SELECT StopCountryCode FROM City WHERE City.StopID = Route.CityEID) AS CountryTo,

Route.NrPolaczenia, Route.LataPn, Route.OdlotPn, Route.PrzylotPn, Route.LataWt, Route.OdlotWt, Route.PrzylotWt, Route.LataSr, Route.OdlotSr, Route.PrzylotSr,
Route.LataCz, Route.OdlotCz, Route.PrzylotCz, Route.LataPt, Route.OdlotPt, Route.PrzylotPt, Route.LataSo, Route.OdlotSo, Route.PrzylotSo, Route.LataNd, Route.OdlotNd, Route.PrzylotNd,
Route.LastUpdate
FROM Operator INNER JOIN Route ON Operator.OperatorID = Route.OperatorID;

It worked and both cities names and both country codes were displayed correctly, but I don't think this is a way how it suppose to be done and I doubt it is efficient code in a long run.

I can't believe that there isn't better way to join two fields form one table to other to pull names for both city codes from City table to Route table.

[June7]

The first query should work. Query builder I design view, drag the City table into the table window twice. Link fields.

Your images don't show OperatorID fields.

[braven]

Your images don't show OperatorID fields.

@June7

My DB got a little bit bigger now, please see Image attached.



Table wise that is how it looks like now.



-----------------------------------------------------------

I guess it was lack of basic knowledge of Access ;-)
My mistake was that I was trying to do double relationship between those two tables in Relationships view. Where I simply should do it in Query builder.

Problem was sorted when I setup Query like this (see image)




June7 thank you for all your help and patience.

I have behaved as complete noob, but I hope I will be able to count for your help in a future ;-)
For my defence I will just say that latest version of Access which I played with, was Access '97 ;-)

[June7]

I thought could set that in Relationships builder. In fact, I have seen situations where Access insisted on duplicating table (and I never figured out why).

Regardless, glad it is working.