Opening Data Entry Form to Certain Record

[JoeTekTips]

Hello,
I have to open a form that will be used to create a new record. Typically, I would just set the Data Entry property of the form to "yes" and would be pretty much set.
However, when the form opens I want to use the default values that are contained in a particular record of the data sourse table. I am sure that there are a few ways to handle this, but I was wondering if someone thinks that they have the best way to do it.
The table is called "tblRates", the primary key is "CustomerName" and the default record is "Customer Quote".
Thanks,
-Joe

 

[willir]

One way would be to set the defaults for the required fields to what you want.

Does the specific record change? Probably not which is perhaps why using defaults is the easier approach.

If it really has to be a specific record, then you can use an event procedure - OnCurrent record on OnInsert...

Dim rst as DAO.Openrecordset
Dim strSQL as String, strQ as String
'I am assuming the primary key is a text string
'If it is a text field you will have to change things a bit
Dim strID as String

'Define / hardcode record you want to find here
strID = [COLOR=blue]"Customer Quote"[/color]
strQ = Chr$(13)

strSQL = "SELECT * FROM tblRates WHERE CustomerName = " _
& strQ & strID & strQ
Set rst = CurrentDB.OpenRecordset(strSQL)

With rst

   If .RecordCount then
      .MoveFirst
      'Change names to match control name on form and 
      'field name on table
      If Len(Me.TextField1OnForm & "") = 0 Then
         Me.TextField1OnForm = !Field1OnTable
      End If

      If Nz(Me.NumberField2Onform, 0) = 0 Then
         Me.NumberField2Onform = !Feild2OnTable
      End If

      'Example of terms
      If Not IsDate(Me.GoodToDate) Then
         'Add 30 days to today's date
         Me.GoodToDate = DateAdd("d", 30, Date)
      End If

      'etc
   End If

   .Close

End With

Set rst = Nothing

Personal comment: Hardcoding, such as "Customer Quote" a value can lead to problems. Another approach would be to define another table and define the defaults in this location. On a multiuser system, you want to keep the basic end user away from the administrative data. And when you move on, the person picking up the pieces may not have a clue on the Customer Quote record.

The above code can be tweaked to accomodate this change.

Richard

 

[JoeTekTips]

Thanks, Richard.
The default record can change from any user level. From the main menu is botton to open a default rates form that allows the default pricing to be ajusted. That form opens to the default record via the following VBA code;

Private Sub cmdOpenDefaultRateForm_Click()

On Error GoTo Err_cmdOpenDefaultRateForm

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmDefaultRateSheet"

stLinkCriteria = "[CustomerName]=" & "'" & Me![Text28] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpenDefaultRateForm:
Exit Sub

Err_cmdOpenDefaultRateForm:
MsgBox Err.Description
Resume Exit_cmdOpenDefaultRateForm
End Sub

.......Where Text28 is a hidden text box on the main menu set to the default value of "Customer Quote"(I couldn't get that stLinkCriteria to work otherwise).
I follow your code and wil give it a shot. The only other idea that I had was to open the form with like the one to edit the default pricing (which would open it to that record) and then in someplace like OnLoad do something like this;

savetxtRate1 = txtRate1
savetxtRate2 = txtRate2
'etc...

DoCmd.GoToRecord , , acNewRec

txtRate1 = savetxtRate1
txtRate2 = savetxtRate2
'etc...

I don't think that checking NZ is that important becuase to form for editing the default pricing requires a least a zero dollar value before saving.

I will post the code that works out. If others have ideas or opinions as well, please post.
Thanks,
-Joe

 

[willir]

The only other idea that I had was to open the form with like the one to edit the default pricing (which would open it to that record) and then in someplace like OnLoad do something like this

If you did this, a person may do something unexpected and inadvertantly over-write the control file.

The default record can change from any user level

What do you mean by this? Any one can change the default pricing values? Or, different people or different customers have different defaults?

Regardless.
It is still your database. However, you want to avoid the end user from accidentally changing things. I feel you should offer a) enter a price quote where the form is populated with the defaults, and b) open the form to edit the pricing defaults.

stLinkCriteria = "[CustomerName]=" & "'" & Me![Text28] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Will allow you to open the form to edit the pricing info. Or you could use...

stLinkCriteria = "[CustomerName]=" & "'" & "Customer Quote" & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

To open the form for data entry, you can use (there are other ways to this)...

stLinkCriteria = "[CustomerName]=" & "'" & "" & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Richard

 

[JoeTekTips]

Sorry if I was not clear...

There is one form that is called frmDefaultPricing, which opens to the record of "Customer Quote". Any user can open this form and adjust the pricing close and save. This form only deals with that one record.

The other form, is one that is for creating a new customer pricing record. The data source is the same as the other form, but I want the form to open with the default values that are present in the "Customer Quote" record (this is why I cannot just hard code it).

I am working on this today and will update the thread will any progess.

Thanks,
-Joe

 

[JoeTekTips]

OK, the following does work for the form that creates a new record of rates for a particular customer;

Fist, I open it with the code (shown above as well);

Private Sub cmdOpenDefaultRateForm_Click()

On Error GoTo Err_cmdOpenDefaultRateForm

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmDefaultRateSheet"

stLinkCriteria = "[CustomerName]=" & "'" & Me![Text28] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpenDefaultRateForm:
Exit Sub

Err_cmdOpenDefaultRateForm:
MsgBox Err.Description
Resume Exit_cmdOpenDefaultRateForm
End Sub


Then, for the on load I do this (also shown above);

savetxtRate1 = txtRate1
savetxtRate2 = txtRate2
'etc...

DoCmd.GoToRecord , , acNewRec

txtRate1 = savetxtRate1
txtRate2 = savetxtRate2
'etc...

So, I do not get directly involved with the record set, but I do get all of the default values in the text boxes of a new record. I think that I am going to keep it coded this way (if it ain't broke don't fix it...right?)
Thank you so much Richard for your input,
-Joe