Solved: Update a record field in one form to another form

[ladysnake38]

New to VBA and I've been working on this part of the program for 4 days now without success.

Basically I have a main form which when a new record is added to a specific field, it opens a second form and the information is added there. What I need to do is take that last entry (from the second form) and update the original field in the main form. The two forms are joined together by an ID field that keeps the second form's multiple entries with the main forms activity.

I have tried queries, different VBA coding, etc. This can't be this hard!:banghead: . It should be a very simple cut and paste between the forms (or tables if that is easier) but I can't get it.

Much Thanks!
Cheryl

[mattj]

Hi!
Ask your question again, except this time talk in terms of controls on your form, and fields in your table.
Let us know a little bit about the table structure and what the relationship is, as well as the names of the forms and controls.
It may be as easy as

Me.txtMyTextBox = Forms!frmFormName!txtAnotherTextbox

It also sounds as if a sub-form might help.

HTH
Matt

[ladysnake38]

Hi Matt:

I really appreciate your help. Below is the code I'm using for this process.

In the Main Form, I have a field called CurrNotes and a Button (Add New Status). When the button is pushed it opens up the second form (which they do not want as a subform). There are three fields within the second form. ID (corresponding to the ID in main form - which is autonumbered), LastDateUp - double-clicking in this field adds the current date, and CurrNotes where the newest update is added. There is a close button which when its pushed, I want the code to save the entry in the second form (which it does), clear the CurrNotes field in the main form (which it does) and copy the newest entry from the second form into the main form field (which it doesn't).

Private Sub Close_Histstat_Form_Click()
On Error GoTo Err_Close_Histstat_Form_Click

'Before the close, take the most current record and place it into the NAR form
'under Last Status

Dim NewCurrRec As String
Dim OldStatus As String

Form_frmntwnar!CurrNotes = OldStatus


If LastDateUp = Date$ Then
Form_frmhiststat!CurrNotes = NewCurrRec
OldStatus = Chr(10) & NewCurrRec

End If

DoCmd.CLOSE
Exit_Close_Histstat_Form_Click:
Exit Sub
Err_Close_Histstat_Form_Click:
MsgBox Err.Description
Resume Exit_Close_Histstat_Form_Click

End Sub


I really appreciate your help. I've only been working here a few weeks and it frustrates me that I can't get this piece done.

Cheryl

[mattj]

This is exactly what a sub-form should be used for. If designed properly, you could even hide and un-hide the sub-form as necessary.

Here begins my rant, and I know that doing what's right isn't necessarily an option, but anywho, here are my thoughts.

You say "they do not want it as a sub-form". If they know what's best for a system, then let them make it. Users should not dictate poor design based on their own ignorance.
Sub-forms are an integral part of an Access database (it makes one - to many relations a breeze to handle).
I have yet to see a robust, functional Access database that doesn't make use of them. Anyone who dictates against their use shouldn't even have the responsibility of participating in the system design, until they have at least a modicum of knowledge regarding the system they want something created in.


As to the problem at hand, why not (for the main form) have a recordsource that is a query that returns the latest note (use the MAX aggregate function in the query on the date field) and just requery the form after a new note is added on the second form?

[ladysnake38]

Hi Matt:

I agree a subform would be a viable option, but alas, it's not to be this time around.

I have redone these forms no less then 10 times. At one point I had a button that just opened the history status file for a specific record. Which they liked. But I couldn't get the entered field (from the main form)to update to the other form (or related table).

The issues with that were duplicate records could be put in and no one here could figure out how to get around that. Like a slight editing change. I'm not familiar with the MAX aggregate. At best I'm a intermediate access user and little VBA experience.

After looking at the code, the error I'm getting when I put the Me.txtCurrNotes = NewCurrNotes is that it can't find the object. I've tried
Form!frmhiststat!CurrNotes = NewCurrNotes, but it didn't like that either.

Should I just scrap this route of thinking?

Cheryl

[mattj]

where are you calling the code from?
Try fulling qualifying the form reference on both sides of the expression
Form!frmhiststat!CurrNotes = Forms!Formname!NewCurrNotes

[ladysnake38]

HI Matt:

This is the entire code for the frmhiststat form. The main form is called frmntwnar. The fields in both forms are called ID, CurrNotes and LastDateUp. The forms are linked by the unique ID.

Basically the frmhiststat has multiple CurrNotes fields with the dates they were entered. That's why I added the If Then statement, so it would pull only the current date of entry.

I have tried a few more changes without success. The record is added to the frmhiststat form and the attached table. The code does clear the field in the main frmntwnar so that the other field can be copied there. But I still can't get the frmntwnar CurrNotes field to accept the contents from the frmhiststat CurrNotes. Since it is clearing the field, I'm guessing there isn't a value for the newCurrNotes string. Maybe this is the problem is that its pulling a null string?

Cheryl

Option Compare Database
Private Sub Record_Number_BeforeUpdate(Cancel As Integer)
End Sub

Private Sub LastDateUp_DblClick(Cancel As Integer)
LastDateUp = Date$
End Sub

Private Sub Close_Histstat_Form_Click()
On Error GoTo Err_Close_Histstat_Form_Click

'Before the close, take the most current record and place it into the NAR form
'under Last Status

Dim OldStatus As String
Dim NARCurrNotes As String


Form_frmntwnar!CurrNotes = OldStatus
Form!frmhiststat!CurrNotes = Forms!frmntwnar!CurrNotes
Forms!frmntwnar!CurrNotes = NARCurrNotes

If LastDateUp = Date$ Then
OldStatus = Chr(10) & NARCurrNotes

End If

DoCmd.CLOSE
Exit_Close_Histstat_Form_Click:
Exit Sub
Err_Close_Histstat_Form_Click:
MsgBox Err.Description
Resume Exit_Close_Histstat_Form_Click

End Sub

[mattj]

You declare the variables OldStatus and NARCurrNotes, but then you don't set them to equal any value.
Also, "Form_frmntwnar!CurrNotes" is not a valid reference to a control on a form. Try "Forms!frmntwnar!CurrNotes" like the other two lines show.

[ladysnake38]

Matt:

Doesn't the below code define the variables OldStatus and NarCurrNotes? If it doesn't, then this is where I'm getting confused on.

Form_frmntwnar!CurrNotes = OldStatus
Form!frmhiststat!CurrNotes = Forms!frmntwnar!CurrNotes
Forms!frmntwnar!CurrNotes = NARCurrNotes


Right now - OldStatus clears the CurrNotes field on the NAR form (frmntwnar), but doesn't update it with anything.

I guess I'm asking if Form_frmntwnar!CurrNotes isn't a valid reference, how is the command

OldStatus = Chr(10)

clearing the correct field (CurrNotes) on the NAR form?

Cheryl

PS: I'm eternally grateful for all your help!

[mattj]

No. Those statement set the value of the control to equal the value of of the variable.
To set the value of the variable, you need something like

OldStatus = Forms!Formname!Controlname

Then you can use the variable

Forms!SomeOtherForm!SomeOtherControl = OldStatus

And as I posted earlier, the first reference "Form_frmntwnar!currnotes" is not correct


OldStatus = Chr(10) is not clearing anything. That is simply setting the variable OldStatus to equal the ASCI character 10.

[ladysnake38]

OK. I'll try that. The Asci (10) is a line feed which I was assuming cleared the field before copying the text into the field.

Thanks again for your help!

Happy Holidays.

[OBP]

ladysnake, can I ask a silly question here?
Why, if you are copying the main form field's data to the second form's field do you need to copy it back?
Unless it has been changed in the second form, which you are not checking for?

[ladysnake38]

Hi Matt:

The flow is that the second form opens and the information is copied there. That entry is then copied to the main form. If there isn't a change, then nothing is done on the activity. An activity will have several updates and they only want the most current to show on the main form.

I've given up the original line of thinking and have written a query, which I hope will resolve this annoying problem. In an update query, can I not use the MAX function (which I haven't found much info on) to choose the most current entry in the second form (based on date) and update the main form with that information?

Cheryl

[DarkSprout]

Some thing that may help: -


'// this will speed-up access time to a field on another form, when you wish to asign variables to their value
With Forms!frmntwnar
.OldStatus = lngOldStatus
.CurrNotes = curCurrNotes
.NARCurrNotes = curNARCurrNotes
End With

'// will empty the Variable
intValue = vbNull

'// find the last value of the AutoNumber field
lngValue = DMax("[anAutoNumberField]", "tbl_TableName")

'// has the record changed?
If Me.Dirty = True Then
'// DoSomething
End IF

And please use Naming Conventions, this makes it a lot easier to debug anyones code.
VBA Naming Conventions Link:
http://www.xoc.net/standards/rvbanc.asp

Enjoy,
=|)arkSprout=

[ladysnake38]

Hi Matt and Darksprout:

Thanks so much for your help. I have kept your code to use at a later time (and believe me) it will come back around!

Currently this issue was resolved with a SQL command with a nested inner join statement. While it does work, it is not without its own issues. And it may well be that the entire database will be redone. In all my research, I found out the design was poor and contributed to a lot of the problems.

I am glad I found this site and there are so many knowledgable people out here that are willing to help. It is much appreciated.

Now...to figure out how to add a login screen!!

Sincerely,

Cheryl