IIf

[shijuantony2002]

yes english is indeed not my first language.. and apologize for the confusion.

the code provided above is not working in normal scenario too as its showing compile error.


To clarify...

The form is for entering new record only. So when any one goes into the form, it will be for entering new records. In that sense, what u said the clarification for new records is rite.

My requirements :-

- I want to allow both fields initially while entering new records, but if i clicked on soonest value date button, then automatically the value date field should get frozen, and the reverse way,
- if value date field is typed then the soonest value date button is frozen. - And, if both the fields are not actioned, then the new records will not be allowed and a message box will be displayed to either click on the soonest value date field or type on value date field.

Also please ignore the originator field as it has no relavance to these two fields where i am working on. I just gave a sample data in public as i guess other displays are not important as what i wanted to achieve is just between value date and soonest value date which are linked to table.

Please let me know, if this clarifies to provide me with the code. Thanks

 

[CameronM]

Hi again,

I have modified and attached a copy of you database that has code that will hopefully achieve what you require.

Firstly, when the form opens, use the On_Current event to enable all the required fields.

Private Sub Form_Current()
'enable all fields required
Me.Option62.Locked = False
Me.[Value Date].Locked = False
End Sub

Next, if the user enters something into the Value Date text field, then lock the option button using the On_Lost_Focus event.

Private Sub Value_Date_LostFocus()
    'test that the Value Date field is null and unlock the Soonest Value
    If IsNull(Me.[Value Date]) Then
        Me.Option62.Locked = False
    Else
        'lock the Soonest Value field since Value Date is not null
        Me.Option62.Locked = True
    End If
End Sub

Lastly, if the user toggles the option button and there is the Value Date field is null, then lock the Value Date text box.

Private Sub Option62_Click()
    'first text that the option button is ticked
    If Me.[Option62] = -1 And IsNull(Me.[Value Date]) Then
        'if the Value Date field is null then insert SVD into Soonest Value
        Me.[Soonest Value Date] = "SVD"
        Me.[Value Date].Locked = True
    Else
        'enable Value Date textbox if the user has un-ticked the option button
        Me.[Value Date].Locked = False
    End If
End Sub

 

[Brianwarnock]

In addition to CameronM 's code in the Form before update event you will need

If IsNull(Value Date]) and isnull(Soonest Date Value) Then

Msgbox This is to output a message to ask for an entry to be made
Read up on message boxes, sorry no time to code and test today.

end if


Brian

 

[shijuantony2002]

Hi Camron / Brian,

Thanks for your efford. This was an ultimate solution to my problem.

I was pasting these codes to my original mdb, however, i got stuck in one of the form.

There was no problem when the code was pasted when the field was named [Value Date], but when i changed the field to [Value Date fx Buy] and accordingly when i changed the below code as below, it showed the error : Compile Error : Method or data member not found

Private Sub Form_Current()
'enable all fields required
Me.[Soonest VD].Locked = False
Me.[Value Date fx Buy].Locked = False
End Sub

Compile Error : Method or data member not found

I had checked all the lines and it also shows up in the List box of the Form, then why it happens when i changed the field from [Value Date] to [Value Date fx Buy] then it shows up with the error.


Thanks

 

[shijuantony2002]

Please ignore my above scrap. I got where i was wrong.

Thanks again.

 

[shijuantony2002]

Hey guyz, my above problem is solved.

This is a fresh query but am continuing in the same thread as the problem is relating to the same but here there is a little twist.

Problem :-

In my above case my form was linked with a table where i had all the required field and against which the code provided by you works perfectly.

But in this case again, my form is linked with a table but at one line i need the code to say that if the field [Conv Curr if other than base] is null then pick up from the query field which is located at Queries -- A(Query name) -- B (Field Name) and input the value of the same field name in the query or else input the field [Conv Curr if other than base].

I had ammended and had framed this code. I know this should not be the code for pulling out queries (marked in bold in the code) but since you guyz had framed the code, am sure you could provide me with the solution again for this.


Private Sub Option65_Click()
If Me.[Option65] = -1 And IsNull(Me.[Conv Curr if other than base]) Then
'if the Conv Curr if other than base field is null then insert query field B as B
Me.[queries]![A]! = Me.[queries]![A]!
Me.[Conv Curr if other than base].Locked = True
Else
Me.[Conv Curr if other than base].Locked = False
End If
End Sub

Private Sub Conv_Curr_if_other_than_base_LostFocus()
If IsNull(Me.[Conv Curr if other than base]) Then
Me.[queries]![A]!.Locked = False
Else
Me.[queries]![A]!.Locked = True
End If
End Sub

Please let me know if you are not clear and i will provide you with a sample db

 

[Brianwarnock]

I was just closing when I spotted this so without much thought - can you not base the form on a query which is the result of joining the table and query mentioned?
else lookup Dlookup

Brian

 

[shijuantony2002]

i need the base as table. just wanted that query line to be on the code to pickup.

I dont know how lookup, dlookup works on code. Can you please provide me with the complete code in line with sample code provided below.

Thanks.

 

[CameronM]

What does your query return? You do not explain how the query knows which records to return, or does it only return one row of records?

As Brian suggested, have a look at the Access help file for DLookup, it will tell you exactly how to set up this code. It also has some great examples to help you. After all how can you learn if we do all the work for you.

As a hint, the syntax for your example would be Me.textbox = Dlookup("FieldName", "Name of Your Query", "Criteria Used to get the right record from the query")

 

[shijuantony2002]

Thanks Cameron,

I did tried in figuring out myself, but somehow it ends up with an error.

The form is for making new entry, so necessarily i have to link it with table.

I had attached the db for your review.

what i require is when i click in Option65 button, and if [Conv Curr if other than base] is null then it will populate field [Base Curr] of the form by looking up the table [Fund Information] in field [Base Currency] and matching with field [Fund Number] against field [Fund Number] of the current Form.

What would be wrong in this code?


Private Sub Form_Current()
Me.[Option65].Locked = False
Me.[Conv Curr if other than base].Locked = False
End Sub
Private Sub Option65_BeforeUpdate(Cancel As Integer)
If Me.[Option65] = -1 And IsNull(Me.[Conv Curr if other than base]) Then
[Base Curr] = DLookup("[Base Currency]", "[Fund Information]", "[Fund Number] = " & [Fund Number])
Me.[Conv Curr if other than base].Locked = True
Else
Me.[Conv Curr if other than base].Locked = False
End If
End Sub
Private Sub Conv_Curr_if_other_than_base_LostFocus()
If IsNull(Me.[Conv Curr if other than base]) Then
Me.[Option65].Locked = False
Else
Me.[Option65].Locked = True
End If
End Sub

 

[Brianwarnock]

No DB attached.
A quick scan of the code noted 2 things
I think it should be Me.[bass curr]

and help never shows [] round the Domain (eg table) name and i have never used them, however i also never have spaces in names.

Brian

 

[CameronM]

What error do you receive?

Put a breakpoint in your code to make sure the value of [Fund Number] is being sent in the DLookup. Also are you sure [Fund Number] is a number, not text?

The other point I need to make, which I know Brian made earier, is that you need to stop naming the controls (textboxes) on you form the same as the fields in your table. How can we tell that [Base Curr] refers to a textbox on your form or to a field in your table?

 

[shijuantony2002]

Brian, had tested already with Me.[] but did not work. I guess [] is used when i have spaces in text of table or field. Does it still not work even if give brackets? I had tested without space and without [] but no output still!! Dlookup code seems to be perfect but didnt know whats more required.

Cameron, i had differentiated my table field names of [Fund Number] to identify clearly. Fund Number is a text.

Had forgot to attach the DB last time. This time i do.

Thanks

 

[Brianwarnock]

I have corrected all of the field names, it is useful to let ACCESS intellisense show you the possible names, ie after typing say me. a drop down list of all possible entries apears , as you type it moves, try it.

I have added Base_curr to the form, and I have corrected the Dlookup.

and changed the event to the double click

I list the code below for you to put into your Db, remember to amend your form

Brian

Private Sub Option65_DblClick(Cancel As Integer)
strdq = Chr$(34)
If Me.Option65 = -1 And IsNull(Me.Conv_Curr_if_other_than_base) Then
Me.Base_Curr = DLookup("[Base Currency]", "[Fund Information]", "[Fund] = " & strdq & Me.Fund_Number & strdq)
Me.Conv_Curr_if_other_than_base.Locked = True
Else
Me.Conv_Curr_if_other_than_base.Locked = False
End If
End Sub

 

[shijuantony2002]

thanks brian,

somehow the intellisence is not working here. I had ammended all the fields as required.

Had also chenged the Query field [Base_curr] but somehow i guess its ignoring the field.

Any idea what would be the problem!

Attached the ammended DB again.

 

[Brianwarnock]

It works ok . the form your new version opens with has USD in the conv_curr etc field therefore does not do the Dlookup
Also the form goes not contain the field Bass_Curr

Brian

 

[shijuantony2002]

the form does not require [Base_Curr] field here. I want the Option65 click to identify that if the table [Conv_Curr_if_other_than_base] is left blank then it should automatically identify the [Base Currency] field which is in [Fund Information] table and by matching up by lookup with the field [Fund] of [Fund Information] table with those of Form Table [MT5BY - Offbook Buy].

This will ensure that when i input text under field [Conv_Curr_if_other_than_base] in the form will remain the same and field [Base_Curr] will goes blank, but when i click Option65 button it will pull the data from table [Fund Information] table against field [Base Currency] and leave field [Conv_Curr_if_other_than_base] as blank.

When i double click [Base_Curr] it pops with Comile Error! Method of Data member not found. It still dont identify field [Base_Curr] at intellisence when i type Me. Can't we reduce it to just a Click instead of double click?

Also am confused what does strdq = Chr$(34) do?

Sorry for too much of question, but just looking to end this problem.

Thanks for your continuous feedback.

 

[Brianwarnock]

Also am confused what does strdq = Chr$(34) do?

Thanks for your continuous feedback.

I will answer this bit because I understand it.

Fund_Number is actually text, therefore it needs to be in "" "", Chr$(34) is " and I believe that what I did is syntactically the easiest way to achieve what is desired.

Brian

 

[Brianwarnock]

Sorry forgot to say yes you can use the single click event, I used the double out of habit with other controls, a single click is sensible here.

Brian

 

[shijuantony2002]

thanks brian, but am still stuck with my main query...