Are there records in Form B yet to match the ID of Form A? If there are (even if there aren't i suppose), are you closing form A when you open form b? you must leave it open so form b can read form a (assuming a relationships is set up). you can open form a *hidden* before opening form b, then when you return, close form b and open form a *normal*. (use window mode if using macro).
yes, I do have records in both of the tables being used. When opening form B, I am hiding the ID code for the rows information. My problem is that when I select the >* to create a new record, the ID field is left empty, and because of this, a new record cant be saved. I'm sure I'm missing a simple point, just not sure where it is.
i think i know exactly what you mean...and you can't use SetValue for the ID field either, right? i usually solve this by using a subform. i came up with another way of doing it but it's convoluted and will wait for someone else to respond first. sry.
Actually setvalue might be the problem since I've never had to use it before. It sounds like it would work (by automatically setting the value of a new record to the information listed on Form A). If this is it, can you provide a link to somewhere good to research how this works, orjust give a general example?
Hi guys, may I join in? I'm sorry but I don't have a double 'z' in my login.
Let me summarize my understanding of the requirement before answering the wrong question, as I have just done on another post. inzzane you have a continuous form (B) that shows all child records for the current ID on form A. On form B you have hidden the control showing the ID. When you navigate to add a new record on form B you cannot complete the operation because the hidden control is empty whereas it should contain the same value as all other rows in the list.
OK, whether the control on form B is hidden or not, and whether form A is open or closed are red herrings: you need some way of detecting that the control is empty and if so inserting the correct value.
I'm not even going to try for a convoluted solution using macros as a solution using VBA is simple and straightforward. First though we need to choose an event on form B when the presence of an ID is tested then we need to devise a way of obtaining the ID value and inserting it into the control on form B.
I suggest the event used is Before Update for form B. You could use On Current but this may lead to adding records containing only an ID.
Now if you can guarantee that form A is always open when this operation occurs then we can retrieve to ID value from form A. However it is safer to poke the ID value in a safe place and then retrieve it from there. The safest place in this instance is form B itself. Here is one way of passing the value to form B.
In design mode for form A select the command button that opens form B. In the Properties dialog select the Event tab and the click in the On Click line. Finally click on the elipses at the right-hand end of the line (the three full stops). This takes you to the VBA code window and directly to the code underlying the command button. Somewhere in the code is a line beginning DoCmd.OpenForm
Following that will be the name of form B some commas and a variable called stLinkCriteria If this is not so then you are opening form B some other way and this write up does not apply.
There are three more optional arguments that may be passed as part of the OpenForm method, the last of which is OpenArgs that is the one we want to use. So after stLinkCriteria type two commas to delineate the first two arguments and then type something like Me.ID where ID is the name of the control on form A that contains your ID value. Close the VBA window and save the design of form A. We're finished with it.
Now open form B in design mode. All we have to do is test for the absence of the ID and then insert it. Select form B (to do this click in the small grey square at the top left corner). In the properties dialog select the Event tab and then click in the Before Update line. Again click on the elipses and Access takes you to the VBA code window and positions you in the Before Update event. This will probably be empty except for Private Sub and End Sub lines. Between these lines we need to add the code to test for and insert the ID.
Now the test for the absence of an ID depends very much on how you have set up the control and database column and whether you have specified any default values. I assume you haven't and suggest some code that takes care of both numeric and text values. So between the two lines type the following.
If IsNull(Me.ID) then
Me.ID = Me.OpenArgs
ElseIf Me.Id = "" then
Me.ID = Me.OpenArgs
Close the VBA`window, save the design of form B and test your system
Rod, thanks for the in-depth help! Thats exactly what I needed to know, as I need to use this on about 5 seperate forms that all work the same way. I've tested it on one, and it works perfect! It also helps repair some issues that I'd created trying to solve it using macros.