Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2009
    Posts
    2

    Unanswered: Unbound fields to table

    Hi guys!

    I'm brand spanking new to Access...so please, throw this dog a bone!

    My problem:
    I have a form with unbound fields. Reason for this is so that I can validate the data before writing it to the table. So when the "save" button is clicked, all fields are verified, and only when all is right the data is saved to the table.

    I have searched for code, and I have tried it, but alas...no luck!

    Can someone please give me sample code that can save the data in die unbound forms to a table??

    thanks so much guys!!!

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Welcome to the forums.

    I've used an approach similar to this in past projects. What I did was to generate an Append Query in VBA based on the user-input, and then ran that. If you need help writing SQL in VBA, I'd have a look at Martin Green's site.

    HTH
    Me.Geek = True

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    My Rule is the Table hold the validate less coding needed also when you add a new form showing differance info the validate rule are always there.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    On small apps, I use the bound approach and just do the validation in the BeforeUpdate and Cancel it if there is an issue.

    Other than that I use VBA recordsets (DAO) to write to the appropriate table on SAVE.

    Code:
    Sub SaveRecord()
       <Validation Commands>
       Dim RS as Recordset
       Set RS = CurrentDB.OpenRecordSet("<SQL to your Table and Record>")
       RS.Edit (or RS.AddNew if it's a new record being inserted)
       RS!FieldName = Me.ControlName
       RS!FieldName = Me.ControlName
       RS.Update
       Set RS = Nothing
    End Sub
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Similar to ST, I use functions to read/write retrieve data to/from the unbound forms. But I don't put the functions in the form (and thus, can't use me.controlname) and I reference the form itself to read/write to the data fields. Utilizing the IsloadedForm function (you can find this module in the code bank) I then use the same function to determine which form to read/write to.

    For example (in ADO), to retrieve a record from the table to the unbound form (passing the CustomerID to the function). Also done from memory so pardon syntax errors (also excuse the spaces automatically put into the syntax when saving this post.).

    Function GetCustomer(CustID as variant)
    if isnull(CustID) then
    msgbox "ERROR: CustID is blank in function GetCustomer!",vbcritical
    exit function
    end if
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from MyCustomerTable where CustomerID = " & CustID & ""
    rs.open strSQL, currentproject.connection,adopenkeyset,adlockreado nly
    if rs.eof and rs.bof then
    rs.close
    set rs = nothing
    msgbox "ERROR: No customer returned in function."
    exit function
    end if
    'check to see what form is loaded (NOTE: this is optional and requires the isloadedForm module.)
    if isloadedform("MyCustomerFormName") then

    Forms!MyCustomerFormName!CustomerID = rs!CustomerID
    Forms!MyCustomerFormName!FirstName = rs!FirstName
    Forms!MyCustomerFormName!LastName = rs!LastName
    ...
    ...
    rs.close
    set rs = nothing
    End Function

    Then to update a record from the unbound form to the table (Note that I typically will have a separate function to Add a Customer due to validation). For the sake of example, assume this function can also add a customer:

    Function UpdateCustomer(CustID as variant)
    on error goto ErrUpdatingCustomer
    if isnull(CustID) then
    msgbox "ERROR: CustID is blank in function UpdateCustomer!",vbcritical
    'Note: or re-direct to adding new customer label/function.
    exit function
    end if
    'Note: Validation logic here....
    If isnull(Forms!MyCustomerForm!FirstName) then
    msgbox "First Name cannot be blank!"
    exit function
    end if
    .....
    'Passes validation....
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from MyCustomerTable where CustomerID = " & CustID & ""
    rs.open strSQL, currentproject.connection, adopendynamic, adlockoptimistic '<= note the difference in how the recordset is opened!
    if rs.eof and rs.bof then 'Note: you can put in your adding new customer method here or make a separate AddCustomer function.
    dim QI as integer
    QI = msgbox("Confirm adding new customer?",vbyesno)
    if QI = vbno then
    rs.close
    set rs = nothing
    exit function
    else
    rs.addnew
    end if
    end if
    rs!FirstName = Forms!MyCustomerFormName!FirstName
    rs!LastName = Forms!MyCustomerFormName!LastName
    ...
    ...
    rs.update
    Forms!MyCustomerFormName!CustomerID = rs!CustomerID 'Note: need to send new CustomerID back to CustomerForm if adding new record!!
    rs.close
    set rs = nothing
    Exit Function
    ErrUpdatingCustomer:
    msgbox "Error in UpdateCustomer function!!........"
    End Function

    You can also get 'fancy' and pass the actual form name to the function to tell it what specific unbound form you want to update to/from but I usually just keep it simple. Otherwise I may check to see what form is opened to know which form to read/write to. And again, I usually have a separate AddCustomer function.

    Note also, you could just pass ALL the fields to the function and then update using the variable names (if there aren't dozens of fields.) Usually though there are a dozen or so fields to update to/from an unbound form. Otherwise it might look like this:

    Function UpdateCustomer(CustID as variant, FName as variant, LName as variant)
    on error goto ErrUpdatingCustomer
    if isnull(CustID) then
    msgbox "ERROR: CustID is blank in function UpdateCustomer!",vbcritical
    'Note: or re-direct to adding new customer label/function.
    exit function
    end if
    'Note: Validation logic here....
    If isnull(FName) then
    msgbox "First Name cannot be blank!"
    exit function
    end if
    .....
    'Passes validation....
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from MyCustomerTable where CustomerID = " & CustID & ""
    rs.open strSQL, currentproject.connection, adopendynamic, adlockoptimistic '<= note the difference in how the recordset is opened!
    if rs.eof and rs.bof then 'Note: you can put in your adding new customer method here or make a separate AddCustomer function.
    dim QI as integer
    QI = msgbox("Confirm adding new customer?",vbyesno)
    if QI = vbno then
    rs.close
    set rs = nothing
    exit function
    else
    rs.addnew
    end if
    end if
    rs!FirstName = FName
    rs!LastName = LName
    ...
    ...
    rs.update
    Forms!MyCustomerFormName!CustomerID = rs!CustomerID 'Note: need to send new CustomerID back to CustomerForm if adding new record!!
    rs.close
    set rs = nothing
    Last edited by pkstormy; 11-09-09 at 21:34.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by myle View Post
    My Rule is the Table hold the validate less coding needed also when you add a new form showing differance info the validate rule are always there.
    I really don't like doing this and wouldn't recommend it. Especially since validation rules can change and this can be a nightmare to troubleshoot if they're in the table design (especially if you're troubleshooting someone else's design.) I prefer to keep all validation out of the table design and control the validation rules themselves within a function or on the form. Once your validation rules exceed what you can do in the table design for 'any' field (which are often), you've essentially then created 2 separate places to troubleshoot for validation and twice the work (not to mention behind the scenes extra processing time on a record.)

    I'm currently working on another developers design where they put what they could for validation in the table design and the rest is in code. Complete nightmare to troubleshoot (not to mention that a lot of the fields are also based on the Lookup tab which also makes it more time consuming.)
    Last edited by pkstormy; 11-09-09 at 22:03.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Note also that there are a couple of posts by izyrider in the code bank which deal with unbound forms.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Nov 2009
    Posts
    2
    jeez guys, the response is great! thank you!!

  9. #9
    Join Date
    Nov 2009
    Posts
    3

    Getting Data from unbound form into table

    Hello,

    Please answer the question asked.

    I originally created tables in which a form was feeding off. However I needed the form to use some arithmetic so I changed the Control Source in order to run the Expression.

    Now the data from the form will not update in the table because it is not linked anymore...

    HOW DO I HAVE USE THE EXPRESSION IN THE FORM AND THEN HAVE THE DATA APPEAR IN THE TABLE????

    Please help.

    Thank you so much.

  10. #10
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by chigley3 View Post
    Please answer the question asked.
    Since you're new to the forums, I'd suggest having a look at our FAQ.

    The question has been answered, with many options offered and often with more than adequate information provided.

    Besides that, I think there are more than a few people around these forums who have qualms with storing calculated fields (it's generally a frowned upon practice).
    Last edited by nckdryr; 11-11-09 at 16:26.
    Me.Geek = True

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by chigley3 View Post
    Hello,

    Please answer the question asked.

    I originally created tables in which a form was feeding off. However I needed the form to use some arithmetic so I changed the Control Source in order to run the Expression.

    Now the data from the form will not update in the table because it is not linked anymore...

    HOW DO I HAVE USE THE EXPRESSION IN THE FORM AND THEN HAVE THE DATA APPEAR IN THE TABLE????

    Please help.

    Thank you so much.
    You DON'T put the expression in the query but instead put it as the sourceobject for a field on the form. This will keep your underlying recordsource for the form still updatable.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Nov 2009
    Posts
    3
    Hello,

    Thank you very much for having the time to reply.

    I haven't put the expression in a query I have put the expression in the form fields 'controlsource'. Where do I find the sourceobject?

    All I have done is on the form, want to multiply to fields together and then have that value update in the table, it shouldnt be that hard should it?

    Please help Im starting to lose hair over this one.

    thank you in advance

  13. #13
    Join Date
    Nov 2009
    Posts
    3
    If it is easier to use the expression in the query I dont mind.

    I am trying to put the expression in the criteria box for example

    =[labour rate]+[labour time] = labour cost

    the expression is not working in the query?

    how could something so simple be so complicated?

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    This:
    =[labour rate]+[labour time] = labour cost

    should just be:
    =[labour rate]+[labour time]

    in the ControlSource of the field on the form.

    Note: if you add this expression to the recordsource query of the form, the query may then become non-updatable. If you put the expression into a ControlSource of a field on the form, your recordsource query for the form will stay updatable.
    Last edited by pkstormy; 11-12-09 at 15:02.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  15. #15
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by klompZA View Post
    I have a form with unbound fields. Reason for this is so that I can validate the data before writing it to the table.
    There are few valid reasons for using unbound forms in Access, and the reason you give is certainly not one of them! I wish a pox on whoever keeps perpetuating this absurd notion! Using unbound forms for data entry means that you are forfeiting at least half of the advantage in using a Rapid Development program such as Access.

    Data validation before writing data to a table can be done quite easily using the Form_BeforeUpdate event. If the validation fails, you simply use Cancel = True to stop the saving of the record and direct the user to make the needed corrections to the data.

    If you insist on using unbound forms, you'd be far better off to use a language such as VB or C++ as a front end and SQL Server or Oracle or some such as a back end!

    This would allow you to make a compiled EXE file which isn't dependent on having a version of Access on the target machines and secures the design of your database, and would allow for far better data security than can be had with Access.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •