Excel to Access record

[desperateUser]

I have an Excel Template that exports data to an mdb. Even though I have the

DoCmd.GoToRecord, acDataForm, "frmLPO", acLast

in my Form_Open event, the form opens to the last saved record, not the record the Excel template just put in the table.

Here is my Excel module:

Sub mcrOpenAccess()

    Dim RetVal
    Dim db As Database
    Dim RS As Recordset
    Dim strOrderID As String
    
    RetVal = Shell("""C:\Program Files\Microsoft Office\Office\MSACCESS.EXE"" ""J:\PROJECTS\PTDb\SRT.mdb""", 1)

    Set db = OpenDatabase("J:\PROJECTS\PTDb\SRT.mdb")

' open the database
    
    Set RS = db.OpenRecordset("tblLPOs")
    
' Add a new record into the database

    With RS
        .AddNew ' create a new record
        
        ' add values to each field in the record
        RS("Account") = Range("Sheet1!F2").Value
        RS("Center") = Range("Sheet1!G2").Value
        RS("Requestor") = Range("Sheet1!D2").Value
        RS("Comments") = Range("Sheet1!I2").Value
        RS("PROJ_ID") = Range("Sheet1!N2").Value
        RS("IBIS-Req") = Range("Sheet1!E2").Value
        RS("VendorNo") = Range("Sheet1!K2").Value
        RS("Contract") = Range("Sheet1!O2").Value
        RS("Date Issued") = Range("Sheet1!B2").Value
        RS("IBISShipTo") = Range("Sheet1!Q2").Value
        RS("IBISFund") = Range("Sheet1!R2").Value
        RS("IBISBuyerInitials") = Range("Sheet1!S2").Value
        RS("WOReqID") = Range("Sheet1!P2").Value
            .Update
            .Bookmark = .LastModified
      
    End With
   
    RS.Close
    
End Sub

This puts everything in the table just fine, but I need the form to open with this recordset so the user can add something to the information in Access. Any ideas where I'm going wrong?

TIA!

Penelope

 

[PHV]

Use the Requery method in access before going to the last record.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[VicRauch]

I assume the DataSource for your form either has a sort on the data, or the DataSource is a table with an index. In either case, it would be very likely that the last record in the recordset would not be the last record added. In order to open the form in Access to the record Excel just added, you could have Access read the index values from the data in Excel, then go to that record. This would involve doing a Set rs = me.RecordSetClone, then using rs.findnext fldindexfield, etc. If that does not make sense, let me know and I'll go a little farther on the explaination.
Vic

 

[desperateUser]

PHV - I'm not sure what I'm doing wrong, but requery doesn't seem to be doing the trick. I put

 DoCmd.Requery "txtUID_LPO"

on the Open Form event. It still gives the wrong record.

VicRauch - I'm a bit confused by the record set clone thing so I'm going to go read the help files.

Thank you both.

Penelope

 

[desperateUser]

Would I use the Set rs = me.recordsetclone code on the On Open event of the Access form (I'm using 97 BTW).

I'm not understanding how to find the last UID_LPO number...not really sure what to use as a criteria that designates it as last?

My brain is tired! I think I'll go home and try this again tomorrow. I seem to be chasing my tail!!

P

 

[desperateUser]

I need more help. I'm just not getting it! I've tried the Requery method and others but nothing I do works.

Please help me :|

Penelope

 

[desperateUser]

The problem here is that even if I put in code to find the last UID_LPO number (I used DMax to find the largest autonumber in the table), the table hasn't written that data and I get the last record saved. I just can't seem to get the form to recognize the record added to the table by the Excel macro!

 

[VicRauch]

Dear Desperate Penelope:

Your table that you are adding records to, what is the PrimaryKey for that table? Is it one field or multiple fields? What ever field(s) make up the key, what is(are) its(their) name(s), and what kind of field(s) is(are) it(they). Such as AutoNumber, Long, Short, Date/Time, etc.

If we know what the unique record key is for the record you just added, we can pass that(those) value(s) to Access to find the record you just added. If your table does not have a unique key, then finding the record will be more difficult, but should still possible.

If the table does not have a unique key, then have you thought of adding these records to a tempory (or work) table? Then your user could open the form for the temporary table, make the necessary additions to the record, and your form could then add the record, with these additions to your "real" table.

Another way, would be to have Access get into your spreadsheet, read all these values, and put them into the form, allow the user to make the additions, then save the record.

Vic

 

[desperateUser]

The PK is an auto number format, field name UID_LPO in the table "tblLPOs", I'm adding a dozen or so fields (account number, requestor, vendor, etc).

I declared a variable LastGS1 as string. Then I did a DMax("UID_LPO", "tblLPOs"). I was doing the Set rs=Me.RecordsetClone and then trying to rs.FindFirst "UID_LPO = LastGS1", but I kept getting errors about object not available. Also, the variable returns the previous PK, not the one just added. Access makes the PK, the rest of the fields are from Excel.

How do I get Access to recognize the record?

This is making me nutz! I really AM "desperate" (and feeling quite dumb!)

Thanks for you help Vic.

 

[VicRauch]

Dear Desperate,
Because your PK is an autonumber field, once you have added the first field of the record within your code in Excel, you can set a variable value equal to your primary key:

' Add a new record into the database

    With RS
        .AddNew ' create a new record
        
        ' add values to each field in the record
        RS("Account") = Range("Sheet1!F2").Value
        [red]Range("Sheet1!Z2").Value = RS("UID_LPO")[/red]

Of course, you can make that "Z2" cell to be any cell you want. Now, in your Access program, all you need do is open your workbook, read cell "Z2", then go into the recordset clone I discussed before to find this record.
I felt it would be much easier to create a small database and spreadsheet to demonstrate the solution. So, here they are:

http://www.vicrauch.com/test/DesperateUser.xls

http://www.vicrauch.com/test/db6_97.mdb

The Access form is looking for this Excel file in your root directory of the C:\ drive. Move it to whereever you would like, then update the path in the code for the form in Access. The database can go anywhere you want. You can put a 2, 3, or 4 in cell A1 of the spreadsheet, save the spreadsheet, then open the form in Access and whatever record key you put into the spreadsheet, that record will open with the form in Access.

Good Luck,
Vic

 

[desperateUser]

Vic, thank you so much for all your explanation - and the files...but I'm not opening Excel from Access. I'm opening Access from Excel. THEN, after the fields from Excel are inserted into the table (and Access auto-increments the PK) Access is supposed to display the record on the form. I just can't get the form to recognize that there's a new record in the table. Does that change the solution?

 

[VicRauch]

You do not need to open the form in Access until after you have done your updating stuff in Excel. Then, you can open the Access form. I'm assuming you are opening that form from Excel. If the form actually opens when you open Access, then I would suggest you close the form and re-open it from Excel. This time, because you are in Excel, you have the PK in Excel (see my last post), so pass an OpenArgument to the form when you open it. This OpenArgument would be the PK. Therefore, the form does not need to open Excel, it would have the PK in it's own OpenArgument.