Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Oct 2007
    Posts
    214

    Question Unanswered: Simple Data Entry

    Hi All,

    I have a table called states and just a simple form with unbound text boxes and a button. I want to be able to enter data in text1 and text2 and when the button is clicked, just have this data entered in the table. The purpose of the form is to just be able to quickly enter data in this table from unbound text boxes. Can this be done? I've tried some code but no avail. Thanks!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Replace the names in this example by the actual names of the objects in your database:
    Code:
    Private Sub CommandSave_Click()
    
        Const c_SQL As String = "INSERT INTO Table1 (Field1, Field2) VALUES ('@1', '@2');"
        
        Dim strSQL As String
        
        strSQL = Replace(Replace(c_SQL, "@1", Nz(Me.Text1.Value, "")), "@2", Nz(Me.Text2.Value, ""))
        CurrentDb.Execute strSQL, dbFailOnError
        
    End Sub
    Note: If the fields in the table are defined as numeric, drop the single quotes characters in the constant (@1, @2 instrad of '@1', '@2')
    Have a nice day!

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Why, for a simple data entry Form, are you going the Unbound route? A Bound Form will do all of this for you, automatically.

    Linq ;0)>
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Oct 2007
    Posts
    214
    I guess I just wanted a simple quick approach as there is really a need to "see" each record because it is used in the "background" of the database...

    The code Sinndho gave works perfect. Thanks!

    Sinndho - can I delete a record the same way? Type the value in the box and click a delete button? Obviously the data I type in the unbound box and want to delete would have to match what is already in the table or I'd receive a message box saying no match. Is there a simple way to achieve this?

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by benz1984 View Post
    The code Sinndho gave works perfect. Thanks!
    You're welcome!
    Quote Originally Posted by benz1984 View Post
    Sinndho - can I delete a record the same way? Type the value in the box and click a delete button? Obviously the data I type in the unbound box and want to delete would have to match what is already in the table or I'd receive a message box saying no match. Is there a simple way to achieve this?
    It's possible indeed. However it would only be safe if the pair of values is unique in the table. A DELETE query will delete all rows matching the criteria (or all rows in the table if no criteria is provided).
    You could use:
    Code:
    Private Sub CommandDelete_Click()
    
        Const c_SQL As String = "DELETE FROM Table1 WHERE (@C);
        Const c_Criteria As String = "(Field1 = '@1') AND (Field2 = '@2')"
        
        Dim strSQL As String
        Dim strCriteria As String
        Dim lngRowCount As Long
        
        If Len(Nz(Me.Text1.Value, "")) > 0 And Len(Nz(Me.Text1.Value, "")) > 0 Then
            strCriteria = Replace(Replace(c_Criteria, "@1", Me.Text1.Value), "@2", Me.Text2.Value)
            lngRowCount = DCount("*", "Table1", strCriteria)
            If lngRowCount > 0 Then
                strSQL = Replace(c_SQL, "@C", strCriteria)
                CurrentDb.Execute strSQL, dbFailOnError
                MsgBox lngRowCount & " deleted.", vbInformation
            Else
                MsgBox "No matching rows.", vbInformation
            End If
        End If
        
    End Sub
    Have a nice day!

  6. #6
    Join Date
    Oct 2007
    Posts
    214
    I get an error and it takes me to this line

    lngRowCount = DCount("*", "StateFaxNumbers", strCriteria)

    Datatype mismatch in criteria expression.

    Any suggestions? Thanks!

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    And what does strCriteria contain?

    Im guessing that one or more of the elements in strCriteria is either text or a date
    If so you must delimit the values

    Mynumericcol = 5.26 and mystringcol = `benz1984` and mydatecol = #2014/01/28#
    Last edited by healdem; 01-30-14 at 04:17.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Oct 2007
    Posts
    214
    one contains a number the other contains a phone number. Not sure if you can help. Thanks!

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So what datatype is the phone number column?
    As said before what is in strCriteria?
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Oct 2007
    Posts
    214
    I'm not an expert programmer so when you say "What is in strcriteria?" could you please elaborate? Sorry to sound not too knowledgeable - still learning. The datatype of the phone number field is a input mask that allows a phone number format. Thanks!

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    How have you defined the column that stores the telephone number?
    Is it text
    Is it numeric

    Im guessing its text, which means the value you supplied as oart of strCritetua must be delimited with either ` or " pairs as suggested previously in post #7.

    The reason for asking what is in strCriteria is that its hard to diagnose what is going wrong as we dont know the actual values you are using. Its very easy to get that information.
    Use the msgbox function
    Or set a breakpoint and step through the code
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Oct 2007
    Posts
    214
    would you be able to give me an example of how / where to delimit? I am not sure where to insert the delimit in this code. Thanks.

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    See post#7
    Or better yet read post#2
    Last edited by healdem; 01-30-14 at 04:19.
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Oct 2007
    Posts
    214
    Easy fix. Changed the format of one text box to general number and removed the '@1' to just @1 as suggested. Works great. Thanks for assisting!

  15. #15
    Join Date
    Oct 2007
    Posts
    214
    What would be an easy way to avoid entering duplicate data. Ie. in the code below if I type in data that is already in the fax number table I would like to be prompted with an are you sure msgbox?

    Private Sub CommandSave_Click()

    Const c_SQL As String = "INSERT INTO Table1 (Field1, Field2) VALUES ('@1', '@2');"

    Dim strSQL As String

    strSQL = Replace(Replace(c_SQL, "@1", Nz(Me.Text1.Value, "")), "@2", Nz(Me.Text2.Value, ""))
    CurrentDb.Execute strSQL, dbFailOnError

    End Sub

Posting Permissions

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