Suggest the last (1 Viewer)

[brewpedals]

Hi,

I have a form used to create a new record. I would like to make data supplied by the user in a previous session the default value. I am strugling.

Here is what I have so far:

Dim lastPercent As Variant
Dim sqlPercent As String

sqlPercent = "SELECT TOP 1 PE_ConstReport.Percent_Compl" & _
             "FROM PE_ConstProjects LEFT JOIN PE_ConstReport ON PE_ConstProjects.Project_No = PE_ConstReport.Project_No" & _
             "WHERE (((PE_ConstProjects.Project_No) = [Forms]![F_Const_Rpt_Main]![lst_ActProj]))" & _
             "ORDER BY PE_ConstReport.Rpt_Date DESC;"
lastPercent = DoCmd.RunSQL(sqlPercent)
txtPercent_Compl.DefaultValue = lastPercent

I get a "compile error: Expected function or variable" Is there an easier method, am I making this a harder problem than it is?

Thanks for your time.

 

[Dugantrain]

Your problem is that DoCmd.RunSql can only be used with Action Statements (i.e. Insert, Append, Update). Second, you're trying to assign an Action to a variable, rather than the result of that action. Assuming that your SQL is correct, you could use a DAO recordset to get this value:

dim db as DAO.Database
dim rs as DAO.Recordset
set db=currentdb
set rs=db.openRecordset(sqlPercent)
txtPercent_Compl.DefaultValue =rs!Percent_Compl
set rs=nothing
set db=nothing

If all of the rest of your code is correct, you should be able to just paste this code over the last two lines from your example and, as long as you have a Reference to the DAO library set, it should run.

 

[brewpedals]

Thanks for your help Dugantrain! It makes sence to me. I'll give it a try first thing in the morning.

 

[Dugantrain]

Well, hang on...looking at your SQL, I see that you will actually get an error. You don't want your form object to be in the string (it looks like you may have copied this from the Query Builder; when you use VBA to write SQL, things are a bit different), you actually want an argument based on that form object passed in. Confusing, I know, but either way:

"WHERE (((PE_ConstProjects.Project_No) = [Forms]![F_Const_Rpt_Main]![lst_ActProj]))" & _
"ORDER BY PE_ConstReport.Rpt_Date DESC;"

should actually be:

"WHERE (((PE_ConstProjects.Project_No) = " & _
[Forms]![F_Const_Rpt_Main]![lst_ActProj])) & _
" ORDER BY PE_ConstReport.Rpt_Date DESC"

 

[brewpedals]

Final Solution

You were right Dugantrain! Thanks for all your help. Here is the code that finally worked.

Private Sub Form_Open(Cancel As Integer)

Dim sqlPercent As String

sqlPercent = "SELECT TOP 1 PE_ConstReport.Percent_Compl " & _
             "FROM PE_ConstProjects LEFT JOIN PE_ConstReport ON PE_ConstProjects.Project_No = PE_ConstReport.Project_No " & _
             [B]"WHERE (((PE_ConstProjects.Project_No)= " & _
             [Forms]![F_Const_Rpt_Main]![lst_ActProj] & _
             ")) ORDER BY PE_ConstReport.Rpt_Date DESC"[/B]
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(sqlPercent)
txtPercent_Compl.DefaultValue = rs!Percent_Compl
Set rs = Nothing
Set db = Nothing

End Sub

 

[brewpedals]

Can I bother you with a follow up question?

Hey Dugantrain, can I impose on you one more time? After successfully setting the default percentage, I went to add code to set a default value for two other fields using the code below.

It sets the default percentage with no trouble, but the second date control is set to "12/30/99" and the third, a memo field is set to "#Name?"

I used Debug.Print to show both the control.DefaultValue and the rs!sqlresult - they both show the correct values. What am I doing wrong?

Private Sub Form_Open(Cancel As Integer)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlPercent As String
Dim sqlDate As String
Dim sqlReport As String

'----------------------------------------------------------------------
' Code to insert the Percent Complete from the last Construction Report
'----------------------------------------------------------------------

sqlPercent = "SELECT TOP 1 PE_ConstReport.Percent_Compl " & _
             "FROM PE_ConstProjects LEFT JOIN PE_ConstReport ON PE_ConstProjects.Project_No = PE_ConstReport.Project_No " & _
             "WHERE (((PE_ConstProjects.Project_No)= " & _
             [Forms]![F_Const_Rpt_Main]![lst_ActProj] & _
             ")) ORDER BY PE_ConstReport.Report_No DESC"

Set db = CurrentDb
Set rs = db.OpenRecordset(sqlPercent)
txtPercent_Compl.DefaultValue = rs!Percent_Compl
Debug.Print txtPercent_Compl.DefaultValue
Debug.Print rs!Percent_Compl

'----------------------------------------------------------------------
' Code to insert the Estimated Completion Date from the last Construction Report
'----------------------------------------------------------------------

sqlDate = "SELECT TOP 1 PE_ConstReport.Est_Comp_Date " & _
          "FROM PE_ConstProjects LEFT JOIN PE_ConstReport ON PE_ConstProjects.Project_No = PE_ConstReport.Project_No " & _
          "WHERE (((PE_ConstProjects.Project_No)= " & _
          [Forms]![F_Const_Rpt_Main]![lst_ActProj] & _
          ")) ORDER BY PE_ConstReport.Report_No DESC"

'Set db = CurrentDb
Set rs = db.OpenRecordset(sqlDate)
txtEst_Comp_Date.DefaultValue = rs!Est_Comp_Date
Debug.Print txtEst_Comp_Date.DefaultValue
Debug.Print rs!Est_Comp_Date

'----------------------------------------------------------------------
' Code to insert the Status Report text from the last Construction Report
'----------------------------------------------------------------------

sqlReport = "SELECT TOP 1 PE_ConstReport.Status_Rpt " & _
             "FROM PE_ConstProjects LEFT JOIN PE_ConstReport ON PE_ConstProjects.Project_No = PE_ConstReport.Project_No " & _
             "WHERE (((PE_ConstProjects.Project_No)= " & _
             [Forms]![F_Const_Rpt_Main]![lst_ActProj] & _
             ")) ORDER BY PE_ConstReport.Report_No DESC"

'Set db = CurrentDb
Set rs = db.OpenRecordset(sqlReport)
txtStatus_Rpt.DefaultValue = rs!Status_Rpt
Debug.Print txtStatus_Rpt.DefaultValue
Debug.Print rs!Status_Rpt
Set rs = Nothing
Set db = Nothing

End Sub

 

[brewpedals]

Solved

I found that txtboxes with a default value of a date (05/04/05) displays 12/30/1899 unless the default date value is inclosed in double quotes.

The solution was to inclose the txtbox.DefaultValue in " 's using the string """", like this:

txtEst_Comp_Date.DefaultValue = """" & rs!Est_Comp_Date & """"


Thanks for your help.

Hey Dugantrain, can I impose on you one more time? After successfully setting the default percentage, I went to add code to set a default value for two other fields using the code below.

It sets the default percentage with no trouble, but the second date control is set to "12/30/99" and the third, a memo field is set to "#Name?"

I used Debug.Print to show both the control.DefaultValue and the rs!sqlresult - they both show the correct values. What am I doing wrong?

Private Sub Form_Open(Cancel As Integer)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlPercent As String
Dim sqlDate As String
Dim sqlReport As String

'----------------------------------------------------------------------
' Code to insert the Percent Complete from the last Construction Report
'----------------------------------------------------------------------

sqlPercent = "SELECT TOP 1 PE_ConstReport.Percent_Compl " & _
             "FROM PE_ConstProjects LEFT JOIN PE_ConstReport ON PE_ConstProjects.Project_No = PE_ConstReport.Project_No " & _
             "WHERE (((PE_ConstProjects.Project_No)= " & _
             [Forms]![F_Const_Rpt_Main]![lst_ActProj] & _
             ")) ORDER BY PE_ConstReport.Report_No DESC"

Set db = CurrentDb
Set rs = db.OpenRecordset(sqlPercent)
txtPercent_Compl.DefaultValue = rs!Percent_Compl
Debug.Print txtPercent_Compl.DefaultValue
Debug.Print rs!Percent_Compl

'----------------------------------------------------------------------
' Code to insert the Estimated Completion Date from the last Construction Report
'----------------------------------------------------------------------

sqlDate = "SELECT TOP 1 PE_ConstReport.Est_Comp_Date " & _
          "FROM PE_ConstProjects LEFT JOIN PE_ConstReport ON PE_ConstProjects.Project_No = PE_ConstReport.Project_No " & _
          "WHERE (((PE_ConstProjects.Project_No)= " & _
          [Forms]![F_Const_Rpt_Main]![lst_ActProj] & _
          ")) ORDER BY PE_ConstReport.Report_No DESC"

'Set db = CurrentDb
Set rs = db.OpenRecordset(sqlDate)
txtEst_Comp_Date.DefaultValue = rs!Est_Comp_Date
Debug.Print txtEst_Comp_Date.DefaultValue
Debug.Print rs!Est_Comp_Date

'----------------------------------------------------------------------
' Code to insert the Status Report text from the last Construction Report
'----------------------------------------------------------------------

sqlReport = "SELECT TOP 1 PE_ConstReport.Status_Rpt " & _
             "FROM PE_ConstProjects LEFT JOIN PE_ConstReport ON PE_ConstProjects.Project_No = PE_ConstReport.Project_No " & _
             "WHERE (((PE_ConstProjects.Project_No)= " & _
             [Forms]![F_Const_Rpt_Main]![lst_ActProj] & _
             ")) ORDER BY PE_ConstReport.Report_No DESC"

'Set db = CurrentDb
Set rs = db.OpenRecordset(sqlReport)
txtStatus_Rpt.DefaultValue = rs!Status_Rpt
Debug.Print txtStatus_Rpt.DefaultValue
Debug.Print rs!Status_Rpt
Set rs = Nothing
Set db = Nothing

End Sub