Option button

[chevyimpala]

I have 2 option buttons on me form, and each button has a insert that it should perform if it is selected. However when I run my code it says that i have entered a expression that has no value. This is what my code looks like. Can anyone help. THX.

If all.Value = True Then

DoCmd.RunSQL "INSERT INTO FundTBL ([Currency],[Size],[Rate],[Date],[VD],[Account]) VALUES(" & Me.Currency.ControlSource & "," & Me.Size * 0.6222 & "," & Me.Rate & "," & Me.Date.ControlSource & "," & Me.VD.ControlSource & "," & Me.Account.ControlSource & &quot"

ElseIf db.Value = True Then

DoCmd.RunSQL "INSERT INTO DeutscheTBL ([Currency], [Rate] ,[Size],[Date],[VD],[Account]) VALUES(" & Me.Currency.ControlSource & "," & Me.Dbrate.ControlSource & "," & Me.Size * 0.0505 & "," & Me.Date.ControlSource & "," & Me.VD.ControlSource & "," & Me.Account.ControlSource & &quot"

end if

 

[hilbertl]

You'd refer to a control with the following syntax me.controlname.value or me!controlname.

H.

 

[ESquared]

If your option buttons are in a frame (aka control group) then you need to refer to the value of the control group, not the individual option (radio) buttons.

Also, since .Value is the default property of controls, you can just leave .Value off and reference the control directly.

It is good practice to give controls a prefix describing their type, similar to declaring variables in VB code. For example, use lngCntr as the name of a counting variable of type long, and use optAll as the name of an option button which means "All."

Hilberl is correct in how to reference a control, but in the class module of a form you can refer to the form's controls without the "Me" qualifier. My opinion is that you should either use the format "Me.Control" or you should name your controls with prefixes. This way your code is much clearer about what is going on.

Assume you have a frame, with two option buttons. The option All is set to the value 1, and DB is set to the value 2. You could then do something like the following:

Select Case fraOption
    Case 1 'All
        'Do something
    Case 2 'DB
        'Do something else
End Select

Last of all, are you truly intending to store the ControlSource of each control? This will simply resolve to the name of the field the control is bound to. If you are looking for the value of each control then leave off ".ControlSource" and you should be fine.

You should pay attention to the use of quote marks and commas in values you insert with SQL. Either write a procedure to scan your values and return a scrubbed string surrounded with double or single quotes, or alternately use a recordset (.Addnew, !Field = Value, .Update).

 

[chevyimpala]

Thank you for your help, what you gave me worked. BUt I have another question for you. I have 5 tables. trial is my main table and deutch, fund, citi, and ubs are my other 4. Now I tried to give each table a autonumber so when i did a delete from trial it would delete all matching records in each table, but i got a message about Invalid feild def. of index or relationship. Now i dont knwo if this is right or not but i made my sub tables a primary key called subnum, which excepts the autonumber as a text feild. Can you see what I am doing wrong here to enforce ref. integrity. BEcause i realized that if i dont have that then my appends want work if i get rid of data from the main table trial.

again thank you for your help to my other question.

 

[chevyimpala]

I forgot, I encountred another problem yesturday while working, I have a column called revalrate in each table, mian and sub. Now everyday the rate will change, and since i have 3 types of currency i am using the rates change for each one. For ex:
currency old new
-------- --- ---
Eur 1.9 1.5
Jpy 1.4 2.3
Chf 4.3 3.5

Now I have a form called REVAL. This form has a combo box, a date box and a update button. Now for some reason, when I try to pass the values of the combo box to each table that has the same currency and date i get a message about object required. This is what my code loks like:

DoCmd.RunSQL "Update DeutscheTBL Set [DBrevalrate] = " & db.DBrevalrate & " Where [Currency] = " & db.Currency & " AND VD = " & db.VD & ""

can anyone help

 

[RoyVidar]

Hi!

Just addressing your sql.

I think one of the reasons is "db". Since you are calling this from the current form, use the "Me" keyword (to refer to controls on the current form). I'm also assuming the control "VD" is a date? In such case two possible issues arise 1. they need date qualifiers (#) 2. if you are using a non US dateformat, it needs to be formatted to a US recognizable format. Another possible thingie here, is the name "Currency" which is a datatype, and should not be used as field or control name, sometimes you might resolve the situation with usin [brackets], but you should rename. Such names is going to keep causing headaches. But - you might be lucky running something like this, if the assuming above is correct:

[tt]DoCmd.RunSQL "Update DeutscheTBL Set [DBrevalrate] = " & Me!DBrevalrate & " Where [Currency] = " & Me![Currency] & " AND VD = #" & format$(Me!VD,"yyyy-mm-dd&quot & "#"[/tt]

- all on one line

Used Ansi date formatting, which, I'm told, should work on any database.

Just curious, you say the rate would change every day, if this update is run next day, you wont find any VD with the same date, unless of course you have added that somewhere else.

HTH Roy-Vidar

 

[ESquared]

chevyimpala,

You cannot join tables by fields that are not the same data type. So, placing the autonumber key from one table into a text field of another table is NOT going to work.

The second table must store the key in a long integer field. After you fix this, you can try going to the relationships editing screen in Access and, after joining the tables properly, turning on "enforce referential integrity" and "cascade update/delete."

As for exchange rates: if the exchange rate changes every day, then perhaps you are better simply storing the value in your primary currency in every table, and doing the currency conversion at view time. Storing previous currency values is problematic, when you know that your data will become obsolete every day and there is an underlying value which does not change. If your primary currency is USD, store the values in USD. Convert them when you need them to be used.

If you must have a history of amounts, it is much more efficient and sensible to create an ExchangeRateHistory table which has one entry per rate change. This way, you can redetermine the currency-converted amount for any item for any date by multiplying the unchanging primary currency field against the rate for the day in question.

If your prices will be changing, you could also create a PriceHistory table. Be warned: keeping a history of changes to data is a difficult chore. The biggest problem is storing the data in a format that is easy to use when the time comes to reconstruct the history.

-E