Mind Blank - Pls Help! (1 Viewer)

[andy_dyer]

Hi,

I'm trying to build something that started life as quite simple but now has grown and grown and grown...

I have a form for projects and a subform form that holds finance details i.e. top level budget etc

On this subform I have a button to open a detail sub-sub-form which shows the annual breakdown and has a button to add new years...

Then on this sub-sub-sub-form is where my challenges arise...

I want the project name, currency and exchange rate to pull through from other forms and they are not currently - I am using this code to try and pull the project ID and Currency ID through

Private Sub Form_Load()

    Dim intProjectID As Integer
    Dim intCurrencyID As Integer
            
    On Error Resume Next ' OpenArgs might be empty
    

    If Not IsNull(Me.OpenArgs) Then
        intProjectID = CInt(Me.OpenArgs) ' ProjectID passed in via OpenArgs parameter in OpenForm method
        cboProjectName.Value = intProjectID
        intCurrencyID = CInt(Me.OpenArgs) ' CurrencyID passed in via OpenArgs parameter in OpenForm method
        cboCurrency.Value = intCurrencyID
    End If
    
End Sub

For the exchange rate this is a calculated field using this code in the control source

=ExchangeRate([Start Date],[CurrencyID])

This works on other forms but may not work here because the currency isn't pulling through...

I've even tried

=[Forms]![frmProjectFinance]![txtExchangeRateUsed]

Just to simply pull through from the main form without re-calculating

But currently nothing gets pulled through...


Any ideas how I can get these pieces of data pulled through would be gratefully received

 

[boblarson]

You currently have this:

   If Not IsNull([B][COLOR=red]Me.OpenArgs[/COLOR][/B]) Then
        intProjectID = CInt([B][COLOR=red]Me.OpenArgs[/COLOR][/B]) ' ProjectID passed in via OpenArgs parameter in OpenForm method
        cboProjectName.Value = intProjectID
        intCurrencyID = CInt([B][COLOR=red]Me.OpenArgs[/COLOR][/B]) ' CurrencyID passed in via OpenArgs parameter in OpenForm method
        cboCurrency.Value = intCurrencyID

So how can you be using OpenArgs in two places for two different fields without some processing to the OpenArgs? OpenArgs only takes one value. So, in order to pass multiple items using OpenArgs, you need to use a delimiter and then you have to use the split function to get the parts and assign them to the right place.

 

[andy_dyer]

Hi Bob,

Thanks for taking the time to reply... you may guess that I've neever used openargs before and also have kind of adapted code I've found on the web to get to where I am...

Now realising multiple items in open args is a no no - I looked and found this but don't know if this is what you meant?

http://www.utteraccess.com/forum/OpenArgs-pass-multiple-t1929209.html

I also don't know where to start trying to adapt it for my database...

 

[vbaInet]

I want the project name, currency and exchange rate to pull through from other forms and they are not currently -

Can you complete this sentence?

Not currently Open? If that was what you were going to say then it's not possible. You will need to use a recordset or a DLookup() function to pull in data.

 

[andy_dyer]

Can you complete this sentence?

Not currently Open? If that was what you were going to say then it's not possible. You will need to use a recordset or a DLookup() function to pull in data.

Hi - no just that they are not currently pulling through...

All my forms are open... the following hierarchy

1) Master Form (fromProjectMaster) which has an embedded subform that displays different subforms based on button clicks

2) the above embedded subform of interest (frmProjectFinance) which has a button to open a new form with annual totals

3) the above mentioned new form (frmProjectFinanceYear) which has a button to open a new form to add new years to the project finances

4) the ultimate form which I need the data in (frmProjectFinanceYearDetail)

The stored project name and currency along with the calculated exchange rate fields are all on 2) above and I need to pass them to 4)

So the forms are all open just not necessarially 'active'

Does that help?

I've attached a cut down version of my database to show my problem...

 

[vbaInet]

Pass the values to form 4) using the Current event and After Update events of form 2).

Me.Form3!Form4!FieldName

 

[andy_dyer]

Pass the values to form 4) using the Current event and After Update events of form 2).

Me.Form3!Form4!FieldName

Hi - sorry I'm confused

I've got this code but it errors saying it cannot find the field named frmprojectmaster... it's nto a field its a form...

Private Sub Form_Current()
Me.txtExchangeRateUsed = Form!frmprojectmaster!frmprojectfinance!txtExchangeRateUsed

End Sub

 

[andy_dyer]

Ok think I've pieced it together

This may not be the best code but it works!

Private Sub Form_Current()
    Dim intProjectID As Integer
    Dim intCurrencyID As Integer
    Dim strExchangeRate As String
    
    strExchangeRate = Forms![frmprojectmaster]!sfrData!txtExchangeRateUsed
    intProjectID = Forms![frmprojectmaster]!txtProjectID
    intCurrencyID = Forms![frmprojectmaster]!sfrData!cboCurrency
    
    Me.cboProjectName = intProjectID
    Me.cboCurrency = intCurrencyID
    Me.txtExchangeRateUsed = strExchangeRate
End Sub

Thanks for your help!

 

[vbaInet]

I hope you put it in the After Update event too?

 

[andy_dyer]

I hope you put it in the After Update event too?

Why do I need to? This code is only when a form loads and there is no field on this new form that would trigger an update of these fields...

Or have I missed something

 

[vbaInet]

Alright, so you just need the values at load time only? That's fine then.

 

[andy_dyer]

Thanks for your help - if I can be cheeky while i know you are online you commented on a related post of mine

http://www.access-programmers.co.uk/forums/showthread.php?t=228968&page=2

I'm still really stuck and can't get this working... :-(

 

[vbaInet]

No probs! Just replied.