add value on button click, by filtering table values ?

[whoknows361]

firstly, thanks all for your feedback thus far.. it has really helped.
table structure:
tbl_ReferralData
ReferralID (PK auto#)
Client Name
Client DOB

tbl_Assigned_Thx
ThxID(PK auto#)
ReferralID
Date TOC sent to billing
Date TOC rcvd from billing


Currently, I have frm_TOC_rcvd
i have a combobox (cmbo_ClientName) and a text field (DateRcvd) in this form. Also have a command button (btn_addDate)

the combo box lists ReferralID, Client Name, and Client DOB, which shows correctly (all from tbl_ReferralData)

I would like a user to click a client name, then enter a date in the DateRcvd text field.

When the user clicks on the command button I would like the Date entered to be added to the field "Date TOC rcvd from billing" in the tbl_assigned_thx. but it will require some filtering

I want the date that is entered in the form to be added to the "Date TOC rcvd from billing" field on the line of data which 1) matches the referral ID as chosen from the combobox and 2) wherein field "Date TOC sent to billing" has a date value in there & field "Date TOC rcvd from billing" has a value of "n/a"

only based on these 2 requirements will the date be entered into the correct field.

Can someone give me some insight onto how I might do this?

Appreciation as always
whoknows361

 

[SeeThru]

Have a look at dlookup() with the correct where clause, it should do the trick.

Other than that, open a recordset with the criteria you need.

set rst=docmd.openrecordset (
SELECT * from tbl_ReferralData INNERJOIN tbl_Assigned_Thx on ReferralID = ReferralID WHERE ReferralID=myform!selectedrecordid AND [Date TOC rcvd from billing]='n/a' and [Date TOC sent to billing] is not null
if not rst.eof then tst.movelast
if rst.recordcount>0 then
[return the values you need]
else
do soemthing else
end if


SeeThru
Synergy Connections Ltd - Telemarketing Services

 

[whoknows361]

SeeThru, thank you for you reply.. im heading in the right direction. unfortunately, I am very weak on VB code.. Here is the code on the button which is in this form we are discussing:

Private Sub btn_AddDate_Click()
On Error GoTo Err_btn_AddDate_Click


    Dim ctl As Control, DL As String
   Dim Msg As String, Style As Integer, Title As String
   
   DL = vbNewLine & vbNewLine
   
   For Each ctl In Me.Controls
      If ctl.Tag = "?" Then
         If Trim(ctl & "") = "" Then
            Msg = "Required Field '" & ctl.Name & "' has No Data!" & DL & _
                  "You'll have to go back and correct this!"
            Style = vbCritical + vbOKOnly
            Title = "Empty Required Field Detected! . . ."
            MsgBox Msg, Style, Title
            Me(ctl.Name).SetFocus
            Exit Sub
         End If
      End If
   Next
      DoCmd.GoToRecord , , acNewRec


DoCmd.SetWarnings False

Exit_btn_AddDate_Click:
    Exit Sub

I am aware that the DoCmd.GoToRecord , , acNew Rec command does not work correctly as it is not filtering the data correctly.

Where do I add the code you specified? and wehre you indicate in your code: "[returns the values you need] and "do something else" ... where would I put in that I wanted it to add the date to the record that was found through the filtering code?

Sorry, but very new at VB. Appreciate your responses thus far.

whoknows361