Access 2003: Want to link a separate form for data viewing/entry

[JeremyI]

I am trying to make a form that mimics the action of a subform; i.e., when the user launches it (via a command button with the caption 'Programme...'), theoretically the new form is passed the value of the record that was being viewed, and then any linked data can be viewed and new records added. But it doesn't work.

This doesn't seem to be a very common thing to do. However, I am not sure how else to proceed, as I've run out of room on my main form!

I'll try to explain better:

The project is a detailed multi-level listing of various details about buildings. My main form, frm3ViewEditAddProperties, contains a general information section and a series of tabs; generally these have been working fine with correctly linked subforms etc. One of my tabs is devoted to a subform called frm4subfrmRooms (linked by parent/child fields PropIndex), which shows the various rooms contained in the building in single-form view with navigation buttons. It already has a sub-subform called frm5subsubRoomFunctions (linked by parent/child fields RoomIndex), which shows the suitable functions the room might be used for in continous-form view. The other thing I need to add is a programme of events that take place throughout the week within that room.

This is where I've run into trouble... I tried to launch a new form, frm6RoomProgramme, that would display the property name and room name already selected in the main form and its subform, followed by a continuous-view subform with details of all of the events in that room. I've called this subform frm7subfrmProgrammeListing and linked master field txtProgrammeRoomIndex (a textbox generated by a DLookUp of rooms matching textbox txtRoomIndex that is already displaying on the main form's subform) to child field RoomIndex.

The approach above means I can read data that is already there, but I can't figure out how to get it to accept new data, presumably because it is linking based on a DLookUp instead of a bound control. Unless I have inserted something at the table level, nothing at all shows in the subform, no matter what permissions the user has. This second form is bound to qry_tblRooms, so I tried adding a textbox (txtRoomIndex) that reflects the primary key directly (RoomIndex), but now I am seeing the "(AutoNumber)" text there, so I realised it is not bound to anything at all.

I tried adapting Microsoft's code that establishes a 'last viewed' setting for the user (stored in a special table), but I'm not very familiar with recordsets yet, so I don't know if that sort of approach translates at all.

Can anyone help?

[yarbrough40]

when you say

...but I can't figure out how to get it to accept new data...

I assume that this is a textbox that you have for users to type something that ultimately saves to a field in your table.... right?

if so then you are correct that you need this to be a bound field. "DLookup" is a read only function and will not accept new record data. Are you accepting user inputted data anywhere else on your master forms or subforms? if so how are you accomplishing this?

[JeremyI]

Yes, yarbrough, I do need to write data to a table, but in this case the user should not be supplying this particular value. It has a relationship to the primary key in tblRooms and the control will eventually be hidden. That's why I need it to get its value from the first form; that way, the new data will be correctly matched to the room that is displaying, for later reference.

Definitely cannot see leaving that step in the hands of the user.

At the moment I have the form (the command button's target form, if you will) bound to tblRooms, which doesn't need to have new data added at all. Maybe I should have the target form unbound? I do need the continous subform so that the user can see the room's entire programme at a glance (or scroll if it has an escpecially high number of events). In the subform is a textbox (txtRoomIndex) that is bound to the foreign key RoomIndex in tblRoomProgrammes, the table that I'm trying to use the form to populate.

Is it possible to use the same bound table for both a form and its subform? Would that perhaps allow me to pass the RoomIndex value to the new entries in tblRoomProgrammes?

Thanks for your help! I'm strating to run short of time on this assignment and so am hoping that a solution can be worked out by the end of the weekend.

[yarbrough40]

If all you need to do is yank a value from the first form, then use this

 
[Forms]![Form1]![Text1].Value 
 

the table/query it is bound to - it doesn't matter, you can bind to whatever you want

[JeremyI]

Yes, I have a couple of textbox controls that do that on this form. (They re-display the building and room names for the user's convenience.)

I guess what I need is for a bound textbox on the target form to select the record from the table (tblRooms) whose primary key matches the index number of the room being displayed in the first form. It needs to restrict the recordset of the target form to that single record so the continuous subform can be related to it.

Is that possible with a bound control? I've tried using "=Forms!frm3ViewEditAddProperties!txtPropIndex " as its default value, which does not work. And of course if I set that as the control source, the textbox becomes unbound.

[JeremyI]

Right, after a bit more reading, I've tried adapting some code that uses DoCmd.OpenForm's WhereCondition property to restrict the output of a report to a single record (I've watched it work correctly in the original source):

    DoCmd.OpenForm "frm6RoomProgramme", , , "qry_tblRooms.RoomIndex = " & _
        "[Forms]![frm3ViewEditAddProperties]![frm4subfrmRooms]![txtRoomIndex]"

But it doesn't work for my form! I'm still seeing (AutoNumber) in the bound field that should now reflect the filtered value.

"[Forms]![frm3ViewEditAddProperties]![frm4subfrmRooms]![txtRoomIndex]" does return the correct value when I check it in the immediate window.

I'm obviously missing something basic... does anyone know the secret?

[TheSmileyCoder]

As I understand your problem you want to open a new form (frm6RoomProgramme) in a seperate window. Make the form have the same recordsource as your initial form. Assuming your tables have a primary key of name "KEY_House", and this value is somewhere in the form (can be hidden) in a textbox tb_KEY_House you could do:

Docmd.OpenForm "frm6RoomProgramme",acNormal,,"KEY_House=" & Me.tb_Key_House",,acDialog

The acDialog will ensure that the form you open (frm6RoomProgramme) will keep focus until closed. Depending on your need, you can set the Datamode to read only, Edit only or Add only. Im guessing you need Edit Only, or Read only, if the new information is entered via a subform.

Now the form will only show the current record.

[JeremyI]

I've given that a try, Smiley, and still no success, I'm afraid.

I'm guessing you mean that tb_KEY_House should be contained on the first form, not the target one (i.e., that's why it is 'Me.tb_Key_House')? I have a comparable control called txtRoomIndex; according to the immediate pane, Access is picking up the value there, but when the target form opens, it is still at a new record in the table. That form's textbox (also called txtRoomIndex), where the selected value should be, continues to display '(AutoNumber)'.

Datamode doesn't need to be set specifically, since the same form will be used by people updating records and merely reading information (Access security permissions are in use).

I'm not sure dialog mode is needed, either; will have to see.

Just stumped...

[TheSmileyCoder]

Setting the datamode is a good way of using the same form for multiple purposes :)

If you attach your db, I can take a look at it for you, the approach I mentioned should work just fine.

[JeremyI]

Thanks, Smiley. I'll have to see if there is any way I can do that tomorrow.

One other thought just occurred to me, though: the target form is bound to a query, rather than a table. Would that cause complications?

The query, qry_tblRooms, is 'SELECT tblRooms.* FROM tblRooms', and I need to use it this way because it is an RWOP query in the secured database. Only the administrator of the database is able to read the tables themselves, and they're linked from the back-end, anyway.

Speak to you soon...

[TheSmileyCoder]

I just saw your post nr #6 which is basicly the same as I tried to do. Assuming your bound textbox is bound to RoomIndex (and not to some dlookup or otherwise) you should not see the "autonumber". I guess if your seeing that its because your form is set to DateEntryOnly. Try looking at the properties of your form, specifically the AllowEdits, AllowAdditions and DataEntryOnly.

[JeremyI]

That's it! Working perfectly now. I must have used a genuine data-entry form as the template when I created this one, and didn't check whether those options were set correctly. Feeling very stupid...

Like I said, something basic. I don't think I'll forget that one quickly!