Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2007
    Posts
    25

    Unanswered: Adding a new record from form contents

    The table's name is [tblPC]

    It only consists of two fields. [myPN] and [myREF]

    On the open form I already have [PN] showing from info from another form, so that would be....

    Me.PN

    That is of course the control name.

    The other is taken from a list box of which is selected, So that would be...

    Me.REF

    I'm just trying to figure out how to take these two and create a new record and put them into their appropriate fields in tblPC.

    Thanks,

    RJ

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You could set up a command button and put some code behind it to write directly to the table ... might be overcomplicating it... I'm not entirely sure I understand what you need here.

    Command button code would be:

    Code:
    Dim rs as RecordSet
    set rs = CurrentDB.OpenRecordset("tblPC")
    rs.AddNew
    rs!myPN = Me.PN
    rs!myREF = Me.REF
    rs.Update
    set rs = Nothing
    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

  3. #3
    Join Date
    Dec 2007
    Posts
    25
    Quote Originally Posted by StarTrekker
    You could set up a command button and put some code behind it to write directly to the table ... might be overcomplicating it... I'm not entirely sure I understand what you need here.

    Command button code would be:

    Code:
    Dim rs as RecordSet
    set rs = CurrentDB.OpenRecordset("tblPC")
    rs.AddNew
    rs!myPN = Me.PN
    rs!myREF = Me.REF
    rs.Update
    set rs = Nothing
    You have the right idea, but for some reason its giving me a type mismatch on the set rs=CurrentDB.OpenRecordset... Line. I just wrote that giving short names for what is reality. I changed the code to represent the actual names. Here is what I have...

    Code:
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("tblProjectContacts")
    rs.AddNew
    rs!ProjectNo = Me.ProjectNumber
    rs!ContactID = Me.Contacts
    rs.Update
    Set rs = Nothing
    I doublechecked the help file for OpenRecordset and it looks like it should work. Hmm.

    Is there any chance this error could have anything to do with its relationship?

    For this table It is simply a relationship between the ContactID (Not primary key) to the Primary key ID of another table. (An AddressBook) Thus you can pull their information from the addressbook, but for only those who match the project they are attached to. Does that make sense? I would think it would error later in the code if that were the case though, not on the first line.

    Any input appreciated.

    Thanks,

    RJ

    EDIT: I thought my answer may reside here. However, I get an entriely different error when I attempt to specifically say...

    Dim DAOrs As DAO.Recordset
    Set DAOrs = CurrentDb.OpenRecordset("tblProjectContacts")


    That gives me User-defined type not defined

    -------
    EDIT:

    I found something else I found peculiar. I tried this...

    Code:
    Dim dbs As Database
    Dim rs As Recordset
    Set dbs = CurrentDb
    Set rs = dbs.OpenRecordset("tblProjectContacts")
    rs.AddNew
    rs!ProjectNo = Me.ProjectNumber
    rs!ContactID = Me.Contacts
    rs.Update
    Set rs = Nothing
    Now it is erroring on the Dim dbs As Database...

    and the assitance that allows to autofill.... I type...

    Dim rs As R

    - and it will show me Recordset as an option.

    I type

    Dim dbs As D

    - Database is not an option there.

    Scratching head. Again with this code it tells me "User-Defined type not defined"

    am I missing something?
    Last edited by rjwebgraphix; 12-13-07 at 17:56.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes, a library reference. Something is wrong with your Tools - References configuration.

    Make sure you have at least the following (Access 2003):

    <see attached image>

    Doing the dim dbs as Database
    Set dbs = CurrentDb
    Set rs = dbs.OpenRecordset()
    is really unnecessary when

    set rs = CurrentDb.OpenRecordset
    Does the exact same thing.
    Attached Thumbnails Attached Thumbnails sshot.jpg  
    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 2007
    Posts
    25
    Quote Originally Posted by StarTrekker
    Yes, a library reference. Something is wrong with your Tools - References configuration.

    Make sure you have at least the following (Access 2003):

    <see attached image>

    is really unnecessary when

    Does the exact same thing.
    Somehow something got corrupt with my access installation on this PC. I cannot get to the References (its greyed out) and portions of my help file is crashing. I'll be re-installing it then re-address this issue.

  6. #6
    Join Date
    Dec 2007
    Posts
    25
    Quote Originally Posted by StarTrekker
    Make sure you have at least the following (Access 2003):
    Ok, Fresh install and the references are back. Must have corrupted something along the way...

    This is Access 2000 and I have everything shown in your attachment, but a question about one of them.

    I didn't have VB for Apps checked, but on this freshly installed version I am seeing 5 lines that say "Visual Basic for Applications"

    Are they all the same thing or do you think there was something left over from the previous installation that I just un-installed and re-installed?

    Thanks,

    RJ


    EDIT:

    Oops, I just looked at each of them and found that all 5 are referencing different things. Which one is needed?

    From top down the location of each of them are as follows

    C:\Windows\System32\MSVBVM60.DLL
    C:\Windows\System32\msvbvm50.dll
    C:\Windows\System32\VBAEN32.OLB
    C:\Windows\System32\VBAEND32.OLB
    C:\Windows\System32\VEN2232.OLB
    Last edited by rjwebgraphix; 12-13-07 at 22:12.

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    It's normal. I have seven of the damn things!

    I've always assumed they are for different applications... word, excel, etc they all have VBA. I also have the different versions of Access on the same computer.
    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

  8. #8
    Join Date
    Dec 2007
    Posts
    25
    Quote Originally Posted by StarTrekker
    It's normal. I have seven of the damn things!

    I've always assumed they are for different applications... word, excel, etc they all have VBA. I also have the different versions of Access on the same computer.
    Thanks, this helped and the code is working now that I was able to add the needed References.

    RJ

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Excellent

    Glad to have helped
    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

Posting Permissions

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