carry over a value in a field to same field in new record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a Table call "CopierPaperUsage" and I need to carry over the value
enter the "UsageAmount" field in one record to "PrevMthUsage" Field in a new
Record.

what i would like: once in the form and once you click to the new record the
"PrevMthUsage" is automatically there and you would on fill out the other
neccessary fields.

any Help will be aprreciated
 
Rich:

You may want to reference the following KB article. Depending on your exact
circumstances, this may be useful as a guide. The article also references a
sample forms database that makes use of this functionality.

http://support.microsoft.com/default.aspx?scid=kb;en-us;210236

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have a Table call "CopierPaperUsage" and I need to carry over the value
enter the "UsageAmount" field in one record to "PrevMthUsage" Field in a new
Record.

what i would like: once in the form and once you click to the new record the
"PrevMthUsage" is automatically there and you would on fill out the other
neccessary fields.

any Help will be aprreciated
 
One trick that I particullary like is to set the defaultValues of the
various controls whose values I need to carry over to the next new
record. In the Form_AfterUpdate event, you'll use code to set the
defaultValues to the actual values....

If IsNull(Me.cboClient) = False Then Me.cboClient.DefaultValue =
Me.cboClient

If IsNull(Me.txtAirline) = False Then Me.txtAirline.DefaultValue =
Chr(34) & Me.txtAirline & Chr(34)

The IsNull's aren't neccessarily required, however in my specific
application they were.

NOTE: The CHR(34)'s are MANDATORY as you're setting the default value to
"myValue". Without the quotes, Access will view the default value as a
variable and give you and error.
 
Sounds like the value needs to be passed using a Function during creation of
the new record. So when the form loads for the new record that field needs to
have its default value set to call the function that would return the desired
value. Example: Text Box:
txtPrevMthUsage Default Value would be

=GetPrevMthUsage()

and that would call a Function to return the value you desire into the Text
Box like this
You will need to filter to the correct record I used the generic RecordID
and called it
CopierPaperUsageRecordID as I do not know what field you would want to
filter this record set with to attain the desired value... maybe a Date
Field, RecordID, Master Key... Anyway this should get you going in the right
direction

Public Function GetPrevMthUsage() As Integer

Dim daoDbs As DAO.Database
Dim daoRec As DAO.Recordset
Dim pstrSql As String
Dim pintMaxRecID As Integer

Set daoDbs = CodeDb

pstrSql = _
"SELECT Max(CopierPaperUsage.CopierPaperUsageRecordID) AS MaxRecID " & _
"FROM CopierPaperUsage;"

Set daoRec = daoDbs.OpenRecordset(pstrSql)

If Not (daoRec.BOF And daoRec.EOF) Then
pintMaxRecID = daoRec(MaxRecID).Value
Else
pintMaxRecID = 0
End If

pstrSql = _
"SELECT CopierPaperUsage.UsageAmount " & _
" FROM CopierPaperUsage " & _
" WHERE CopierPaperUsage.CopierPaperUsageRecordID = " & pintMaxRecID & ";"

Set daoRec = daoDbs.OpenRecordset(pstrSql)

If Not (daoRec.BOF And daoRec.EOF) Then
GetPrevMthUsage = daoRec(UsageAmount).Value
Else
GetPrevMthUsage = 0
End If

pstrSql = ""
daoRec.Close
daoDbs.Close

End Function

Take Care & God Bless ~ SPARKER ~
___________________________________________________________________
 
Back
Top