Excel Spreadsheets into access form

[mickeege]

Hi all,

Hope you can advise me...

I'm fairly new to access and i've got a form which displays contact and basic information for each supplier I use. I also have an excel spreadsheet for each supplier named [supplier_code].xls where supplier_code is an 8 digit supplier code.

Is it possible for an access form to display the relevant spreadsheet depending on which record is open in the access form?

Also, the primary key of the table which feeds the form is called Supplier_Code if that helps.

Thanks in advance.

 

[JHB]

..
Is it possible for an access form to display the relevant spreadsheet depending on which record is open in the access form?

Yes it is, but why not have the info about the supplier in the database!

 

[mickeege]

I have the spreadsheets which are updated by one group of people and the database is accessibly by another group but strictly on a read only.

I know it would be a much better situation to have the info within the database and something I could do, but this is how I need it to be and I don't know how to do it.

 

[JHB]

When do you want to show it, when you cycle through the records, when you click a button or ... ?
And where do you want to show it, at the same form, in another form, in Excel or ...?

 

[mickeege]

I've got on the main form a button which takes me to a new form and would like to display the spreadsheet within this form.
Thanks

 

[JHB]

I've got on the main form a button which takes me to a new form and would like to display the spreadsheet within this form.

Then create an Unbound Object Frame in the form, call it OleUnboundSupplier.
Place the below code in the form's Load event.
Transfer the supplier_code using the Open arguments.

  Dim ctl As Control
  
  Set ctl = Me!OleUnboundSupplier
  With ctl
    'Enable control.
    .Enabled = True
    ' Set Locked property to False.
    .Locked = False
    ' Allow embedded objects only.
    .OLETypeAllowed = acOLEEmbedded
    ' Specify the OLE server and the type of object.
    .Class = "Excel.Sheet"
    ' Specify the file to be embedded.
    .SourceDoc = "C:\Access programmer\" & CStr(Me.OpenArgs) & ".xls"
    ' Create the embedded object.
    .Action = acOLECreateEmbed
  End With

 

[mickeege]

Sorry for being a bit slow on the uptake here but how would I transfer the supplier_code using the Open arguments?

The form where supplier_code resides is called Supplier_Entry and the new form is called Terms.

Thanks

 

[JHB]

Below is a link to the DoCmd.OpenForm, look at the last argument.
http://msdn.microsoft.com/en-us/library/office/ff820845(v=office.15).aspx