1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access 2010 Kinda stuck here ;-)

Discussion in 'Business Applications' started by filipvds, Oct 21, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. filipvds

    filipvds Thread Starter

    Joined:
    Oct 21, 2013
    Messages:
    45
    Hi,

    I'm trying to find a solution for a problem I have.
    I have a database (access 2010) where data is stored of clients. Each client has a seperate data entry. If people are married or living together they have still a separate data entry.
    To avoid mistakes, I'm trying to limit double entries of data (such as address).
    For example, I could use the field with the address of the husband as data source for the field of the address of the spouse. But I also need the possibility to enter the address of the spouse myself (if both are not living on the same address). If I use the field of the husband as datasource I cannot change the address of the spouse without changing the address of the husband.

    I would like to have the possibility that, if I leave the field of the spouse blank it is automatically filled in with the address of the husband, but I also need the possibility to fill in that field myself with a different address.

    I hope I make sense?


    Thanks
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,659
    filipvds, welcome to the Forum.
    How are the 2 currently linked, ie if I want to use VBA code to enter the spouses address, (which you have already entered) how do I know which record to look at?
    I have created a database like that in the past and the records are linked via a key field, so you would select the spouse in a combo and it would copy across the spouses address, but it could still be over written if required.
     
  3. filipvds

    filipvds Thread Starter

    Joined:
    Oct 21, 2013
    Messages:
    45
    The two are linked by a file number they share. there are two collumns where the address is stored. One collumn contains the street name and number the other collumn contains the zip-code and city.

    Not really sure I'm understanding what you did with your DB (i'm a access novice :))
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,659
    Can you give the name of the table and fields involved and we can develop some VBA code to enter the data.
     
  5. filipvds

    filipvds Thread Starter

    Joined:
    Oct 21, 2013
    Messages:
    45
    Hi,

    The table is called "V-research". The field with the common number for husband and spouse is called "work number" and the address is divided in two fields "street_number" and "zip_city".


    Thanks in advance
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,659
    I suggest to test this code that you create a Command Button, do you know how to that?
    If you can you then replace the VBA code with this code

    Dim rs As Object, SQL As String
    On Error GoTo Eventerror
    SQL = "SELECT V-research.* " & _
    "FROM V-research " & _
    "WHERE work_number = " & Me.work_number
    Set rs = CurrentDb.OpenRecordset(SQL)
    Me.street_number = rs.street_number
    me.zip_city = rs.zip_city
    rs.Close
    Set rs = Nothing
    Exit Sub
    Eventerror:
    MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

    depending on how the work number is stored it might need this code instead

    Dim rs As Object, SQL As String
    On Error GoTo Eventerror
    SQL = "SELECT V-research.* " & _
    "FROM V-research " & _
    "WHERE work_number = '" & Me.work_number & "' "
    Set rs = CurrentDb.OpenRecordset(SQL)
    Me.street_number = rs.street_number
    me.zip_city = rs.zip_city
    rs.Close
    Set rs = Nothing
    Exit Sub
    Eventerror:
    MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

    which converts it in to text. As I can't test this first, you may get an error message, if you do write it down and post it on here.
     
  7. filipvds

    filipvds Thread Starter

    Joined:
    Oct 21, 2013
    Messages:
    45
    Hi,

    Thx for the effort you put in this.

    Since this is a work in progress there are some changes in naming of the fields. They have been put in Flemisch now. The table is now called "TBL_OOST-VLAANDEREN". The field with the common number for husband and spouse is called "Nummer_Brussel" and the address is divided in two fields "Adres" and "Woonplaats".

    I've tried to change the field names in the code you have sent. I put the code between the two commands that were there already (in red)

    Private Sub Command57_Click()
    Dim rs As Object, SQL As String
    On Error GoTo Eventerror
    SQL = "SELECT TBL_OOST-VLAANDEREN.* " & _
    "FROM TBL_OOST-VLAANDEREN " & _
    "WHERE Nummer_Brussel = " & Me.Nummer_Brussel
    Set rs = CurrentDb.OpenRecordset(SQL)
    Me.Adres = rs.Adres
    Me.Woonplaats = rs.Woonplaats
    rs.Close
    Set rs = Nothing
    Exit Sub
    Eventerror:
    MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

    End Sub

    But i get error #: 3131 Syntax error in FROM clause. This error I get also with the second code.

    Private Sub Command57_Click()
    Dim rs As Object, SQL As String
    On Error GoTo Eventerror
    SQL = "SELECT TBL_OOST-VLAANDEREN.* " & _
    "FROM TBL_OOST-VLAANDEREN " & _
    "WHERE Nummer_Brussel = '" & Me.Nummer_Brussel & "' "
    Set rs = CurrentDb.OpenRecordset(SQL)
    Me.Adres = rs.Adres
    Me.Woonplaats = rs.Woonplaats
    rs.Close
    Set rs = Nothing
    Exit Sub
    Eventerror:
    MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
    End Sub


    By the way an example of work number (Nummer_Brussel): 201305635


    Thx
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,659
    Can you supply me with a copy of the database with either dummy data or no data in it.
     
  9. filipvds

    filipvds Thread Starter

    Joined:
    Oct 21, 2013
    Messages:
    45
  10. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,659
    OK, it is the Table name causing the problem, Access VBA does not like the "-" in the name, so you have to put the name in square brackets.
    I have added a button to the form with the complete code that you will need, which includes ensuring that there is something in the Nummer_Brussel and also a matching record in the table.

    The code in the button can actually go in the After Update event procedure off the Nummer_Brussel field so when you enter the number it will carry out the check.

    To test the current version enter a Nummer_Brussel that matches one in the table and then click the button.
     

    Attached Files:

  11. filipvds

    filipvds Thread Starter

    Joined:
    Oct 21, 2013
    Messages:
    45
    Hey this looks great :). That was what I was looking for.

    I just could not save the form. I got following message "this form or report contains changes that are incompatible with the current database format. The form or report was not saved. In order to save your changes, you must remove any layouts that have empty cells in them and/or set the HasModule property for the form or report to No."

    I changed the HasModule of the form and I could save it. Is that OK?


    Thx
     
  12. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,659
    Yes that should be OK, my version of Access 2007 didn't like the form very much either :D.
     
  13. filipvds

    filipvds Thread Starter

    Joined:
    Oct 21, 2013
    Messages:
    45
    (y) Thx a lot for providing me with the solution. Great job :)
     
  14. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1111211

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice