Opening to specific record but creating new if none exist. 2

[jasonmac]

Hello everyone. I'm doing this in access '97.

I am using the following code to open a form to a specific record.

DoCmd.OpenForm "sbfrmActionItems", acNormal, , "[RFQNumber] = '" & Me![RfqNumber] & "'"

This works fine but if a record doesn't exist I want it to create a new record with the specific RFQNumber(Me![RFQNumber]). How can I accomplish this?

Thanks in Advance,
Jason McElhaney

 

[cranebill]

Well on the form you are using to open the second form you could test the value of Me!RFQNumberLike this:

Dim Con As ADODB.Connection
Dim Rs As ADODB.Recordset
Set Con = CurrentProject.Connection
Set Rs = New ADODB.Recordset

Rs1.Open "SELECT RFQNumber From YourTable Where RFQNumber = " & Me![RfqNumber], Conn2, adOpenDynamic, adLockReadOnly

If Rs.Bof Or Rs.Eof = True Then
DoCmd.OpenForm "sbfrmActionItems", acNormal
Forms!sbfrmActionItems.DataEntry = True
Else
DoCmd.OpenForm "sbfrmActionItems", acNormal, , "[RFQNumber] = '" & Me![RfqNumber] & "'"
End If

Set Rs = Nothing
Set Con = Nothing

HTH

Bill

 

[cranebill]

Oops

Rs.Open "SELECT RFQNumber From YourTable Where RFQNumber = " & Me![RfqNumber], Con, adOpenDynamic, adLockReadOnly

Replace with this

Bill

 

[KenReay]

Hi

send the RFQ number in .OpenArgs

DoCmd.OpenForm "sbfrmActionItems", acNormal, , ,,Me![RfqNumber]

in the OnOPen Event of sbFrmActionItems

Me.RecordsetClone.findfirst "[RFQNumber] = '" & Me.openargs & "'"

If Me.recordsetclone.nomatch
DoCmd.GoToRecord , , acNewRec
Else
me.bookmark = me.recordsetclone.bookmark
end If






Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[jasonmac]

Hi Ken. This statement is giving me a type Mismatch error.

DoCmd.OpenForm "sbfrmActionItems", acNormal, , ,,Me![RfqNumber]

Any idea why?

Thanks again,
Jason

 

[jasonmac]

[RFQNumber] is a text field in both tables.

 

[RoyVidar]

[tt]DoCmd.OpenForm "sbfrmActionItems", acNormal,,, ,,Me![RfqNumber][/tt]

Five commas, not four (by trying to type this, or using F1, it should easily have been discovered)

Roy-Vidar

 

[jasonmac]

Thanks Ron and everyone else. It's opening the form but it is NOT creating the record I want. I want the new record created to be populated with the RFQNumber.

Say It is the first time I am opening this form while I am on a particular RFQNumber. I do not want the user to have to retype the RFQNumber into the form that opens. This is the most important part of the problem.

Thanks,
Jason

 

[RoyVidar]

Then on the on current event of the form, set the control to the openargs:

[tt]If Me.recordsetclone.nomatch
DoCmd.GoToRecord , , acNewRec
Me!txtNameOfYourControl.Value = Me.OpenArgs
Else
me.bookmark = me.recordsetclone.bookmark
end If[/tt]

Roy-Vidar

 

[RoyVidar]

Ooops - typo again - not the on current, of course, but the on open event, as KenReay describes - hmmm - need new glasses;-)

Roy-Vidar