Open Form with an if

Locopete99 · Dec 15, 2017

Hi Guys,

I have a form that I want to open, but there are 2 conditions.

1. If a record with the same record in the field Subba exists, then open that record.
2. If the record doesn't exist, open the form and auto populate the records with e record set.

I can do all of this, but stuck on the open if. My try was to have the following on an On Open.

Code:

IF "[Subba3]=" & "'" & [Subba] & "'" Then
DoCmd.OpenForm "Frm_Over", acNormal, , "[Subba3]=" & "'" & [Subba] & "'"

Else


**Do the record set**

End If

Can someone help point me in the right direction.

Thanks

moke123 · Dec 15, 2017

Just to clarify, Are you saying that if a matching record exists open the form with that record and if there is no matching record open the form in data entry mode?

Locopete99 · Dec 15, 2017

Hi,

Yes, kind of.

If the record exists then open the form and view the record.

If the record doesn't exist, open the form and run the following;

Code:

Set rsSubba4 = CurrentDb.OpenRecordset("tbl_over", dbOpenDynaset)

With rsSubba4
        .AddNew
        ![Subba3] = SBA
        ![Blanket Agreement Number] = BAM
        ![Account Number] = ACT
        ![Part Number] = PTN
        ![Net Selling Price] = NSP
        ![Total Forecast] = TF
        ![Salesman] = SMN
        ![End Date] = ND
        ![Months Remaining] = DateDiff("m", Date, ND)
        .Update
        End With

moke123 · Dec 15, 2017

assuming the fields SBA, BAM, etc, are on the calling form and you want to add the record and then open the form with the new record in it, you have to add the record first.

I would add a variable and grab the PK of the new record. something like..

![End Date] = ND
![Months Remaining] = DateDiff("m", Date, ND)
MyNewID = .Fields(0)
.Update
End With

then open your form with the variable as your criteria

Locopete99 · Dec 15, 2017

yeah I can do that.

The thing that I'm having problem with is that if someone goes back into the same record on that form, I don't want it to create a new record, I want it to open the original record.

static · Dec 15, 2017

The string in your IF is the WHERE clause for a recordset or dlookup.

Code:

addnew = currentdb.openrecordset("select 1 from tbl_over where [Subba3]='" & Subba & "'").eof
if addnew then
    'record doesn't exist add it
else
    'record exists
end if

The_Doc_Man · Dec 15, 2017

Part of your problem is that in the OnOpen event, the state of the controls is ambiguous at best. Use OnLoad, which follows OnOpen, but at that point, the controls are all available and you can put logic in that event to redirect where you want to start. You DON'T want to wait as long as the OnCurrent event (follows OnLoad) because at that point you have already populated the bound fields.

static · Dec 15, 2017

He's checking if the record exists before opening the form.

Open Form with an if

Locopete99

Registered User.

moke123

AWF VIP

Locopete99

Registered User.

moke123

AWF VIP

Locopete99

Registered User.

static

Registered User.

The_Doc_Man

Immoderate Moderator

static

Registered User.

Similar threads

Users who are viewing this thread