Add a value to the underlying table field of a form using field on

[aquabp]

When I add a new record to my form I want to use the datefield to populate another field on that record using different formatting andadd that value to the underlying table. The problem is that I do not knowenough VBA to make it happen. The code below came from a previous question onthe forum. I am trying to adapt it to for this solution but no luck.

Private Sub MSort_Enter()
'Code Purpose
                'Change Date format to MSort Format “yymm”
                'Add MSort Value to underlying Table
                ‘Move to next field.
                ‘Main purpose is to avoid keying mistakes. MSort is a monthly lookup value in forms and reports. 


Dim rs As DAO.Recordset
Dim RD As Integer
If IsNull(Me.MSort) Then
    
    DoCmd.SetWarnings False
    'finds theMSort value
    'Set rs = CurrentDb.OpenRecordset("SELECT NRec FROM 350_IF01Q02")
    ' RD = Format(Date, "mmyy")
    'Set rs = CurrentDb.OpenRecordset("SELECT RDate FROM 300_ReceiptS")
    Set rs = currentdb.openrecordset("SELECT[RDate], Format([RDate],"yymm") AS MSort FROM 400_ReceiptS;")
             'Line above does not work debug wants to put a list separator at yymm

    'Places the above MSort Value into the MSort Control
    Me.MSort = rs!MSort
    'Me.MSort = RD
    
    Me.VSort.SetFocus
Else
    Me.VSort.SetFocus
End If

Any help would be greatly appreciated!

[ItsMe]

This does not make sense because it is grabbing every record from the table and is only grabbing one column, NRec.

'Set rs = CurrentDb.OpenRecordset("SELECT NRec FROM 350_IF01Q02")

If you want a specific field, use the field column name and the unique record. To get to the correct record, use the OpenRecordset method on an SQL statement that retrieves a single record or use the FindFirst method of the resulting recordset.

rs.findfirst "[KeyFieldName] =" & MyVariable
MyOtherVariable = rs![AnotherColumnName]

[aquabp]

Hi Itsme!
From the first comment the parentheses means it is not used in the code, but this statement is pulling from a max value query that results in a single value. The problem is that the new record has not been updated to the table yet so the query does not find anything. The findfirst method is a search method right? Would I not have the same issue?

[ItsMe]

If 400_ReceiptS is a query that retrieves only one record, you can do something like this.

Set rs = currentdb.openrecordset("400_ReceiptS", dbopensnapshot)
dim strResult as string
strResult =  format(rs![RDate], "yymmm")
msgbox strResult

If RDate is not a of type Date, you will get an error on
strResult = format(rs![RDate], "yymmm")

[aquabp]

The 400_ReceiptS is a table that the 350_IF01Q02 queried and used in this line, Set rs = CurrentDb.OpenRecordset("SELECT NRec FROM 350_IF01Q02")
Its been a frustrating afternoon.
I tried playing around with 'strResult = rs.Fields.getValue("RDate") put the error message said method was not found. I also tried
Set strResult = rs!db.OpenRecordset(RDate.Value)
Which did not error out but I didn't know how to get it to yymm format and it did not put a value in MSort anyway.


In a query you would create a field name: format(RDate,"yymm") and your done.
We are entering data on a record. The first field manually keyed is the date field. Now we want it to convert to a MSort format using VBA so we do not get keying errors.
This afternoon I was trying to grab the RDate value and assign it a var1 then assign Var1 to Var2 and format Var2 = format(Var1,"yymm"). From all I have read I am not sure I understand it but it sounded like it should work. I just do not understand how it would work. Is this off base?

[ItsMe]

What is the SQL of the query that retrieves the record you need?

[aquabp]

Set rs = currentdb.openrecordset("SELECT[RDate], Format([RDate],"yymm") AS MSort FROM 400_ReceiptS;") but I get an error message and it is not specific to the current record. Even if I updated the record before entering the MSort Field I would have to get the Receipt value from the this record to query. Right? If we can do that then we should be able to get the RDate value, format it and deliver it to the MSort Field. Right?

[aquabp]

I looked at the getvalue method this afternoon but when I put it in the code I get an error message say this method was not found. Is there a way to out put a value or a getvalue to a message to see if the value is actually being captured?

[ItsMe]

I am still curious if you have a query that retrieves a single record where the value of Rdate is the desired value. Can you post that SQL here?

[aquabp]

No, I don't have a query. I would have to update the record to the table, then get the receipt value from this record to query this single record. Right?

I use this in the default field in a form that I have open up from the notinlist event. =[Forms]![400_ReceiptF01]![400_ReceiptF03].[Form]![ISort].[Text]
Can we capture the RDate using something like this? Can we assign something like this to a variable?

[aquabp]

I tried this in the forms Default value for the MSort field.
=IIf([MSort]=0,Format([Forms]![400_ReceiptF01]![400_ReceiptF02].[Form]![RDate],"yymm"),[MSort])
IT did not work either.

[ItsMe]

It seems as though you are throwing everything except the kitchen sink at this. If you want to update a table with a value from another table, you are going to need to know which column and which row this value resides. You do not have to have a query. You could, maybe, use a DLookup() function.

A query might look like
SELECT RDate FROM 400_ReceiptS WHERE KeyFieldName = "SomeVariable"

[aquabp]

Ok, I'll try that. To note I am trying to update a field from another field in the same table.

[aquabp]

Hi ItsMe
Ok, I couldn't get that to work. I tried to update the line so the sql statement could find it but could not get down to the single record without including the receipt field which is the primary field so I went and did the google to learn more about sql statements and variables. Long story short I came up with this code.

Dim RD As String
Dim MS As Long
    
    If (Me.MSort) = 0 Then
    
        'finds the RDate for the MSORT field
        RD = Screen.ActiveForm.RDate
        MS = Format(RD, "yymm")
    
        'Places the above NRec into the GSort Control
        Me.MSort = MS
        DoCmd.GoToControl "VSort"
    Else
        Me.VSort.SetFocus


End If

It works but not sure if I am missing something that will haunt me in the future. This is not a multi user form yet. I'll cross that bridge one day I hope! If you could take a look at his and see if you see any issues I would appreciate it! Thanks again for your help!!

[ItsMe]

Ok, I'll try that. To note I am trying to update a field from another field in the same table.

Then, include that column in the Recordsource of the form.