A few different problems...

[J.Windebank]

Hi guys, this is my first post here, hope someone can understand my babbling and offer some advice

OK, well I am setting up an Access 97 database at work, and have little to no training in the program, so most of my results end up being workarounds at best, so long as the database does as intended.

Now I will try and lay out the basics of this as simplistically as I can. I work for a market research company, and we measure free-to-air tv programs.

I need to add all of the TV data to the database, on a week by week basis, for each day of the week, and for each state we measure in (6).

Here is my table structure so far.

tblChannels [ChannelID (autonumber), ChannelName (text)]
tblMain [ID (autonumber), Round (text), ProgramID (long integer), Day (text), TimeFrameID (long integer), Time (long integer), StateID (long integer)]
tblPrograms [ProgramID (autonumber), ProgramName (text), ProgramCode (text), ChannelID (long integer)]
tblStates [StateID (autonumber), State (text)]
tblTimeFrame [TimeFrameID (autonumber), TimeFrame (text)]
tblTimes [TimeID (autonumber), Time (text)]

Now I have a form called frmAddPrograms that I use to add programs to the database, and it just adds the Program Name, the Code, and the Channel. This is fine.

My trouble begins with the main form, named frmTVCards.

Now I will try and explain how I want the form to work, what it does currently, and then will finally ask the questions I have.

When you first enter the form the cursor starts at "txtCOTRound" and you enter a four digit nuber that relates to the round being worked on (round = week number). From there you tab to the Day and drop down to choose, and then the same for state.

Each week there will be 42 seperate sets of records, one for each day (7), for each state (6), for each COT Round (week).

Then you would tab through Program Name, Timeframe, and Time which would loop as you added all of the appropriate tv programs for that day.

The little "+" button next to the program name opens that frm AddPrograms form to add any new programs.

Now the questions.

1) When I add a new entry, I would like it to be displayed straight away in the sub form. At the moment the sub form works on a query that gets all of the relevent info, and the query works when run on its own, but I do not know how to get it to auto update in the form, can anyone suggest a way to "refresh" the table?

2) Whenever I add a new entry, all of the fields are wiped blank again for a new entry. I understand that this is supposed to happen, but was wondering if there was some way I could get the COT Round, Day, and State info to always remain the same as the last record in the table? I think the only way would be a query to extract the info from the last record in the "tblMain" table, but then how do I get it to show in the form?

3) After adding a new program in the "frmAddPrograms" form, which is opened through the "frmTVCards" form, when I close it the combo box list does not update, what command can I use to refresh the combo box after closing the other form?

4) Finally, eventually I want to output all of this date into word. I am not really asking for help at this stage, as I have not even attempted it, but can anyone point me in the direction for tutorials/info on how to write data from Access to Word?

I hope I have made this all clear, and that someone can help me figure this out.

Thanks in advance for everyone who takes the time to read this.

Jordan Windebank

 

[Mile-O]

1) When I add a new entry, I would like it to be displayed straight away in the sub form. At the moment the sub form works on a query that gets all of the relevent info, and the query works when run on its own, but I do not know how to get it to auto update in the form, can anyone suggest a way to "refresh" the table?

Don't know how your VBA skills are but you can write a line of code using the Requery command and apply it to the subform after a specific event.

2) Whenever I add a new entry, all of the fields are wiped blank again for a new entry. I understand that this is supposed to happen, but was wondering if there was some way I could get the COT Round, Day, and State info to always remain the same as the last record in the table? I think the only way would be a query to extract the info from the last record in the "tblMain" table, but then how do I get it to show in the form?

VBA again - this time before you add a new record, you create a couple of variables to store the information you wish to keep, add the new record, and then populate the required fields with the variables.

3) After adding a new program in the "frmAddPrograms" form, which is opened through the "frmTVCards" form, when I close it the combo box list does not update, what command can I use to refresh the combo box after closing the other form?

Again, using the Requery command.

 

[Oldsoftboss]

How big is the Db at the moment. Maybe posting a cutdown zipped demo (with ficticious data - (under 100k) would help.

I'm sure we can help to solve at least some of the issues

Dave

 

[Oldsoftboss]

Hope this will help with Q2
Dave

 

[Oldsoftboss]

PS - Great looking form.

Just need to enter the form name in the form properties under the "Caption" field

I also have code to add custom icons for each form. Go to http://users.impulse.net.au/predator/accessdownloads.htm and download the Form Icon zip.
I know this is getting ahead of your questions, but nothing like a bit of a diversion when the programming seems to be going around in circles
Dave

 

[J.Windebank]

OK thanks for the help so far guys, I appreciate it...

Mile-O-Phile: My VBA skills are only meicore at best, but I will try and see if I can figure out your suggestions.

Oldsoftboss: Thanks for the code to change the icon, interesting, may employ it at a later date. Also thank you for the sample database though I have to admit I could not figure out what it was actually suppose to show me, sorry.

I am attaching the database with a few minor changes, thanks guys.

 

[J.Windebank]

Just a bump back onto the first page.

Anyone got any suggestions at all?

Also, does anyone know where I can find a reference for MS Word DDEExecute commands that can be used in VBA in Access?

Thanks

 

[Oldsoftboss]

The Db I posted copies the last record on the subform into the next new record in that column.

Sorry if I'm on the wrong track.

BTW Some will have suggestions for your Db. Dont stress
Dave