Results 1 to 8 of 8
  1. #1
    Join Date
    May 2012
    Posts
    6

    Unanswered: Help with VBA Code on Access Form

    Hey guys,
    I am trying to write a VBA access code that will help with displaying an error message to user trying to input new record into table, if 4 fields contain identical data. I have been working on this for 2 days straight now, reading others post, and NOTHING seems to stop that record from inputing into the table.

    Table Name: tblAwardsLog
    4 fields: LastName , FirstName , Recommended , DateInitiated

    All fields are TEXT fields.. even DateInitiated

    I have this Awards Log almost to where I want it, but just can't seem to get this one thing to work right.

    Fairly New to VBA so easy to follow step by step would be Awesome!!!!

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Doing this kind of thing with mixed Field types can get complicated (at least for me ) but with all Text Fields it's fairly easy:
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    If DCount("*", "tblAwardsLog", "LastName & FirstName & Recommended & DateInitiated = '" & Me. LastName & Me. FirstName & Me. Recommended & Me. DateInitiated & "'") > 0 Then
     Cancel = True
     MsgBox "A Record With These Details Already Exists!"
    End If
    
    End Sub
    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

  3. #3
    Join Date
    May 2012
    Posts
    6

    Help with VBA Code on Access Form

    Thanks Missingling on that Code...It worked Great!!!

    Is it possible to make it where it only checks the first name for the FIRST 3 Characters for Duplicates??

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    If I understand you correctly:

    For the Field name itself

    Left(FirstName, 3)

    For the Control Name

    Left(Me.FirstName, 3)

    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

  5. #5
    Join Date
    May 2012
    Posts
    1

    Saving Form into table

    Hi All,
    I'm newbie for MSAccess and VBA. I'm trying to get efficient in MsAccess. ok this is my "little" project which I've been working on.

    I created the Forms with several combo boxes. Each combo box is connected to separate table so that i can choose the value from each Access Table. Also, I have Text box which to write the comment.

    And I created Command Button (for save the Form).

    So, ideally when I clicked to "save" button, the Form with current selected value from each combo box will save in the another table.

    I tried with Macro but couldn't able to save.


    I'd really appreciate if someone could help me with my problem..

    Thanks!

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    The Comboboxes have to be Bound to Fields in the Table or Query that your Form is based on in order for them to be Saved to the Table.

    In Form Design View
    • Right-Click on the Combobox
    • Click on Properties
    • Click on the Data Tab
    • Click into the Control Source Property box
    • Use the Dropdown Arrow to select the appropriate Field

    Repeat this for each Combobox in question.

    In the future, please start a thread of your own, with an appropriate title, when posing a question! Your problem has absolutely nothing to do with the subject of this thread, and hijacking someone else's thread is inappropriate behavior!

    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

  7. #7
    Join Date
    May 2012
    Posts
    6

    Exactly!!!!

    Missingling..That was EXACTLY what I was needing!!! Thanks so much, finally finished with the project that was going to make ALL my hair turn grey and fall out

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad we could help!

    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

Tags for this Thread

Posting Permissions

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