DLookUp with option group and If statements question

[d9pierce1]

Hi all and Happy Easter to those that celebrate it....

I am making some changes to my db now that i am using it daily and i have run into something i need some assistance with... Please

I have a few forms where I either receive funds or purchase from a person or business. I want to make this just a list. OK
So option 1 = business, option 2 = Person
TxtBox = PurchaseName where i would type in a name of a business or person and this would have a NIL function
If yes, then Add name, If no, Undo

! Not sure if to put this in before update or after update?

Next I want it to seek the name i entered to see if its in the tblBusiness if option Business or tblPerson if Person is selected.


Something like?

If Business option, DLookUp BusinessID, tblBusiness, BusinessName
If the BusinessName exists, then
CmdView.Caption = View Business Details
If BusinessName does not exist, then
CmdView.Caption = Add Business Details
Else If Person option, DLookUp PersonID, tblPerson, PersonFullName
If the PersonFullName exists, then
CmdView.Caption = View Person Details
If PersonFullName does not exist, then
CmdView.Caption = Add Person Details

This possibly in the after update or after insert of the txtBox entry? Not real sure

If in the NIL function, i select No, then I want it to exit sub and not perform the DLookUP...
If in the NIL function I select Yes, then I want the DLookUp to fire, I also want to have the
DLookUp fire if I enter a name that is in the List....
And then I will put the necessary code on the button click to determine what it needs to do if clicked.

I am not real sure how the code would look for the DLookUp with the If or IIf possibly so could I please
get some assistance on this.

Thank you
Dave

[Micron]

Not sure I followed all of that but would suggest
- option buttons bound to option frame to filter list to one type or the other
- FAYT (find as you type) combo. If list results does not contain the value being entered, fall back to nil event.

Not seeing the need for buttons/altering captions, DLookups in that situation. Not really seeing the need for the option buttons either, unless you really have 1 table for person and another for businesses.

[d9pierce1]

thanks Micron
Yes, I do have two tables, one for business and one for person....
yes, options bound....
FAYT combo is a yes....
Not in list is simple...
The part that I dont get is how to do the button.... If it exist, then I want the caption to be View Business or Person Details which will open up the form for that table. If
Doesnt exist, then I want button caption to be Add Business or Person which will open to a new record in selected table...
I may have to try this different ways till i get it to work, was just looking for some assistance on how that dlookup would look like with the if conditions?
The reason I am asking as some what nieve of the process or code to first NIL, then if condition met, change buttons.. and where to place, weather in Before Update / After Update
or before Insert / After Insert....

Thanks again
Dave

[orange]

I agree with micron re FAYT. You may also consider Select Case when resolving IF conditions.

Be cautious when using DLoookup

Although criteria is an optional argument, if you don't supply a value for criteria, the DLookup function returns a random value in the domain.

[Micron]

Something along the lines of

Private Sub frmFrame1_AfterUpdate()

If Me.frmFrame1 = 1 Then '1st option button was chosen
  If DLookUp("BusinessName", "tblBusiness", "BusinessID=" & Me.cmbBusinessID) <> "" Then
    Me.CmdView.Caption = "View Business Details"
  Else
    Me.CmdView.Caption = "Add Business Details"
  End If
  Exit Sub 'or do something else
End If

'similar person related code comes next

End Sub

The above wont' execute if the frame value is 2 (assuming you assign values 1 and 2 to the options) because "person" option would have been chosen. Thus the code could simply repeat as above but for person table. Since you can only have one click event for the same button you'll have to read its caption property to know what to do in the button click event. It also assumes your lookup criteria is numeric and that I have the table and field names correct. Also note that even if you do provide criteria and there are 2 or more records that satisfy it, the record it returns will still be random. I've only ever found it to be the 1st record that satisfies the lookup but I would not rely on that at all.

IMO you'd learn more by researching - in this case if I were you I might Google "ms access alter button caption" and "ms access DLookup with criteria. Then take a stab and post some code if you remain stuck. It helps to help you as it often eliminates a lot of guesswork, such as object names, and provides more clues as to what needs to happen and when. Plain English can substitute for code where you might have no clue as to how to write it. If you don't use code tags (see # button on posting toolbar) and proper indentation, you make it harder to read code and I'm sure I've seen posts where a responder bows out because of that.

[d9pierce1]

thank you Micron,
I will play with this and see what happens.
Again, thanks