Controls on Forms in Access Project

  • Hi,

    I am trying to decide whether to build a form in Access Project either:

    1. using bound controls (ie. text boxes, combo boxes, etc) related to a view in SQL Server 2005, or

    2. whether to load the data programatically (using code) into unbound controls on the form, and then saving the data programatically also.

    Does anyone have any experience or opinion on pros and cons with either method.

    Any assistance would be appreciated.

    Thanks

  • It depends!

    How many users will be using the FE?

    How many records will be in the BE?

    Your network speed?

    Etc.

    I make lots of FE applications(over 200 in last 18 months).

    I have found that Choice 1 is easiest to work with.

    If you have a fast network and less than 40,000 records.

    If your BE contains more than 100,000 records, then Choice 2 is the better way to go.

    Once you have the SPs for you Add, Delete, and Modify written, and you have your screens designed, it's not to bad.

    "When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
    "Robert A. Heinlein"

  • Hi,

    Thanks for the reply. There are currently between 20-50 users (and growing) and number of records is growing and will be above 40,000 is some of the tables eventually. Network speed can be an issue, and would prefer to do all I can to keep this from being an issue (past experiences!).

    So option 2 is preferable after your input. Especially as the Stored Procedures will keep things precompiled and faster.

    One more question though. When retrieving and updating data using the stored procedure, it seems that I will have to still assign each control with a value, and then retrieve the value from the control when updating. This will take one line of code per control (ie. fieldinstoredprocedure=ors.fields("controlname")). Is there a simpler way to write the code for assigning and retreiving the value from the control?

    Thanks again

  • This routine does it automatically for you - make the name of the control the same as the related column in the returned recordset, and put the word "Load" in the Tag property of the control.

    This example runs the stored procedure, passing a parameter from a combobox, then assigns the recordset to the form, then binds each of the controls to the relevant column. It runs from the AfterUpdate event of the combobox, but you could run it from form load etc.

    Private Sub cbobrand_code_AfterUpdate()

    On Error GoTo err_handler

    Const ProcName = "cbobrand_code_AfterUpdate"

    Dim ctl As Control

    Dim rst As ADODB.Recordset

    Dim strSQL As String

    strSQL = "EXEC dbo.usp_SelectBrand @brand_code = '" & cbobrand_code.Value & "'"

    rst.Open strSQL, mcnn, adOpenKeyset, adLockOptimistic, adCmdText

    Set Me.Recordset = rst

    Set rst = Nothing

    For Each ctl In Me.Controls

    With ctl

    'Auto fill the text boxes from the recordset

    'where the control's name

    'equals the recordset field's name.

    If ctl.Tag = "Load" Then

    ctl.ControlSource = ctl.Name

    End If

    End With

    Next ctl

    End Sub

  • oops - duplicate post

  • Hi Chris, let's suppose the control name is "Customer", then

    ctl.ControlSource = ctl.Name

    sets the Customer control's ControlSource to "Customer"

    but then...why not just set ControlSource via the control's property sheet?

  • Thanks for your reply Chris, but I think the solution you gave may be for Bound controls (unless I misunderstood).

    I am looking to implement unbound controls where they are populated/updated by form code and a stored procedure.

    Although, I have a lot of fields in the sp, which means that I would need to have a variable in the sp for every field when updating. Is there an easy way to assign values through an UPDATE stored procedure without creating separate variables for each field in the sp?

    Thanks for all the help so far.

  • jwellington (9/22/2009)


    Thanks for your reply Chris, but I think the solution you gave may be for Bound controls (unless I misunderstood).

    I am looking to implement unbound controls where they are populated/updated by form code and a stored procedure.

    Although, I have a lot of fields in the sp, which means that I would need to have a variable in the sp for every field when updating. Is there an easy way to assign values through an UPDATE stored procedure without creating separate variables for each field in the sp?

    Thanks for all the help so far.

    The form starts as unbound, but the routine I posted assigns a recordset to the form on change of a combobox, then binds the controls on the fly - it prevents you getting error messages when the form loads, as at that point it doesn't have a recordsource

    This is a bit of the best of both worlds - you get the convenience of a bound form, but bound to s stored procedure on the fly - no need to bind to a view or table.

    As for saving/updating the data, I do it by stored procedure, passing in each of the text box contents as parameters

    Select Case strCRUDType

    Case "C"

    strAction = "Insert of store"

    .CommandText = "dbo.usp_tblStore_insert"

    .Parameters.Refresh

    .Parameters("@store_number") = txtstore_number

    .Parameters("@brand_code") = txtbrand_code

    .Parameters("@brand_level_id") = txtbrand_level_id

    .Parameters("@store_name") = txtstore_name

    .Parameters("@salutation") = txtsalutation

    .Parameters("@town") = txttown

    .Parameters("@address") = txtaddress

    .Parameters("@post_code") = txtpost_code

    .Parameters("@iso_country_code") = cboiso_country_code

    .Parameters("@telephone1") = txttelephone1

    .Parameters("@telephone2") = txttelephone2

    .Parameters("@fax") = txtfax

    .Parameters("@email") = txtemail

    .Parameters("@live_store_ind") = chklive_store_ind

    .Parameters("@manual_address_ind") = chkmanual_address_ind

    .Parameters("@closed_date") = txtclosed_date

    .Parameters("@delivery_type_code") = cbodelivery_type_code

    Case "U"

    strAction = "Update of store"

    .CommandText = "dbo.usp_tblStore_update"

    .Parameters.Refresh

    .Parameters("@store_number") = txtstore_number

    .Parameters("@brand_code") = txtbrand_code

    .Parameters("@brand_level_id") = txtbrand_level_id

    .Parameters("@store_name") = txtstore_name

    .Parameters("@salutation") = txtsalutation

    .Parameters("@town") = txttown

    .Parameters("@address") = txtaddress

    .Parameters("@post_code") = txtpost_code

    .Parameters("@iso_country_code") = cboiso_country_code

    .Parameters("@telephone1") = txttelephone1

    .Parameters("@telephone2") = txttelephone2

    .Parameters("@fax") = txtfax

    .Parameters("@email") = txtemail

    .Parameters("@live_store_ind") = chklive_store_ind

    .Parameters("@closed_date") = txtclosed_date

    .Parameters("@delivery_type_code") = cbodelivery_type_code

    .Parameters("@manual_address_ind") = chkmanual_address_ind

    .Parameters("@time_stamp") = txttime_stamp

    Case "D"

    strAction = "Delete of store"

    .CommandText = "dbo.usp_tblStore_delete"

    .Parameters.Refresh

    .Parameters("@store_number") = txtstore_number

    .Parameters("@time_stamp") = txttime_stamp

    End Select

    .Execute lngRowCount, , Options:=adExecuteNoRecords

    If .Parameters("@RETURN_VALUE") 0 Then

    ci_UpdateDb = False

    blnInTrans = False

    mcnnParam.RollbackTrans

    'See if a message was returned

    strMsg = ""

    For Each prm In .Parameters

    If prm.Name = "@message" Then

    strMsg = prm.Value

    Exit For

    End If

    Next prm

    MsgBox strAction & " failed with return value ." & .Parameters("@RETURN_VALUE") & vbCrLf & vbCrLf & strMsg, vbOKOnly + vbCritical, "CI Error"

  • Access is notorious for its problems working with a lot of users over LANs (I understand though that you will be using it as a front end only).

    I have developed a lot of applications both ways. If you are confortable with VB then I would suggest to go with unbound controls.

    Easier to scale, easier to debug, reliable, but longer to develop.

    Plus I prefer to have full control over when and what happens in my app.

    Having said that, it ultimately your choice !

  • Hi,

    Thanks for all the responses. I have decided to go with the unbound controls but have another question.

    Apart from the benefit of pre-compiling with stored procedures, could I load the data from a view, and then UPDATE on that view to save the data. I have tested this and it seems to work, but thought I heard somewhere that you could not UPDATE a view, or that there may be negative consequences.

    Thanks again.

  • You can update and insert to views - I do it regularly, but the underlying table must have a unique index to be updatable

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply