Pulling Data From Previous Record to Fill Subform

[Ash1402]

Hi,

I have a subform that is embedded in a main form. The database itself is a few years old and so I think redesigning is out of the question. What I am wondering is if there is any specific code that can go into the tables themselves and populate the form based on the previous record.


I would like for the Transfer to Rehabber fields (To and Permittee) to be duplicated on the Transfer From Rehabber fields (From and Permittee) on the next record. Do you think that there is code that will be able to achieve such a thing?

I have attached a quick view of the subform in order for you to take a look at and see where each field is.

Thank you for any help

[orange]

see Allen Browne's sample with subquery
see
Get the value in another record

at http://allenbrowne.com/subquery-01.html

[RuralGuy]

At the Table level...NO. At the Form level then this should help: http://access.mvps.org/access/forms/frm0012.htm

[Ash1402]

Thank you for both responses!

In the second response, do you have any recommendations on using the code. For example, there is a field called FNAME in my form that I used to try this out on. I placed a button on my form and named the button btnDuplicate. After that I wrote the following code:

Private Sub btnDuplicate_Click()
Const cQuote = """" 'Thats two quotes
Me!FNAME.DefaultValue = cQuote & Me!FNAME.Value & cQuote
End Sub

I changed only FNAME in where it said "control" previously. Nothing happens when clicking on the button and I'm not sure where to start to even begin fixing this.

[RuralGuy]

You would notice the effect when moving to a new record.

[Ash1402]

Thank you so much for the solution RuralGuy! It worked!

I have one additional request though utilizing the same image above. In some instances I would like to take the data in the To/Permittee fields and put it in the From/Permittee in the next record. I have used similar code:

Private Sub ToFrom_Click() Me!NameFrom.DefaultValue = cQuote & Me!NameTo.Value & cQuote
Me!PermitteeFrom.DefaultValue = cQuote & Me.PermitteeTo.Value & cQuote
End Sub

Unfortunately when I click the button the code is on and move on to the next record "#Name?" is displayed in both the NameFrom and PermitteeFrom fields. Is there a way to fix this? I know the reason behind it becomes because NameTo is now empty but is there something such as taking the contents only and placing that in there?

[RuralGuy]

I see no reason to get the #Name? error unless you have not spelled one of the names correctly. Use the "." instead of the "!" and intellisense will give you a selection to pick from. When you press the button the fields are not empty, right?

[Ash1402]

I tried to use the dots. I also went through and double checked the spelling. The code is on the 'To/From' button. When clicked I was hoping that the second record below would show Alina in the From field and Ann in the Permittee field but I get the #Name? error.

[RuralGuy]

Are those ComboBoxes on the Form? What do they have as a ControlSource?

[Ash1402]

Access says they are combo boxes at the top. The control sources are names? I assume that they are names of the fields in the table maybe but they are 'NAME FROM', 'NAME TO', 'PERMITTEE FROM' and 'PERMITTEE TO'.

In the row source there are what I believe are SQL statements or queries of some sort. The permittee boxes link to the same query as do the name boxes.

They all say Table/Query in the row source type field.

Thanks for your help so far RuralGuy!

[RuralGuy]

Are the "DefaultValues" in the RecordSource of the RowSource of the ComboBoxes?

[Ash1402]

I'm not sure how to answer the question :S. This is the contents of the 'Row Source' for both the name columns:

SELECT DISTINCTROW [Sub-Permittees and Permittees (All Rehabbers)].[MEMBER NAME] FROM [Sub-Permittees and Permittees (All Rehabbers)];

The permittee one is similar. I guess the query populates the cells. I had luck with setting the value of the from field to the value of the to field by using:

Me!PermitteeFrom.Value = cQuote & Me.PermitteeTo.Value & cQuote


So the problem is coming by making it happen in the next record but I can't figure out why.

[RuralGuy]

I would try putting the value you want in an invisible TextBox on the form and then copy it when you get to the next record. Use the .Value rather than the .DefaultValue as a test.