Open second form to select record

[esskaykay]

Hello,

I’m having a problem open a second form based on data in the first form. I want to open frmGrades when frmPermits closes to the record containing the same Permit_No. To open frmGrades accordingly I coded:
DoCmd.OpenForm "frmGrades", , , "[Permit_No] =" & Forms!frmPermits!Permit_No

I believe the problem is I must first save the Permit_No to tblGrades prior to opening frmGrades (Insert Into). However, it looks like that is working. So maybe I’ve got something wrong with my DoCmd.OpenForm

Here’s my CODE:

If Me.Grades = -1 Then
If Me.Request_No > 0 Then
GoTo GoodBye
Else
DoCmd.RepaintObject

DoCmd.RunSQL "INSERT INTO tblGrades ( Permit_No, RequestDate, RequestTime, Location, Contractor, RequestBy, Phone, ReceivedBy, OwnerName, Sidewalk, Driveway ) SELECT tblPermits.Permit_No, tblPermits.PermitDate, txtTime, tblPermits.Par_Addr, tblPermits.Contractor, tblPermits.RequestBy, tblPermits.Phone, tblPermits.ReceivedBy, tblPermits.Ownername, tblPermits.RepairSWK or tblPermits.ReplaceSWK or tblPermits.NewSidewalk, tblPermits.ReplaceApproach or tblPermits.NewApproach FROM (tblPermits INNER JOIN tblLegals ON tblPermits.Notice_ID = tblLegals.Notice_ID) LEFT JOIN tblContractor ON tblPermits.Contractor = tblContractor.Contractor WHERE ((([tblPermits.Permit_No])=[forms]![frmPermits]![Permit_No]))"

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenForm "frmGrades", , , "[Permit_No] =" & Forms!frmPermits!Permit_No
End If
Else
End If

Any suggestions would be appreciated.
Thanks,
SKK

 

[pbaldy]

What is the data type of Permit_No? Do you get an error, or ?

 

[missinglinq]

Also, what event is this code placed in?

 

[esskaykay]

Permit_No is a test field (XX-XXX). But that may be the problem. Is the DoCmd syntax different for text than numeric? I may be able to revise my tables a bit and create a numeric field to query on.

SKK

 

[esskaykay]

Ultimatley it will be placed in the OnUnload event but as a test I placed the code on a command button

 

[pbaldy]

See here for the different syntax required for text:

http://www.baldyweb.com/wherecondition.htm

 

[esskaykay]

Supurb - that did it. I will keep this for future reference.

Thank you very much once again,
SKK

 

[esskaykay]

Hello,
One more thing…

When I click a button on the frmGrades, I want to open frmPermits based on the data in the field Request_No. Request_No is a numeric field.

If I code:
DoCmd.OpenForm "frmPermits", acNormal, , "[Request_No] =" & Forms!frmGrades!Request_No

In most instances it works fine – except if there is no data in the tblPermits.Request_No field. It then returns a completely blank form (i.e., no fields). When I close the form, I receive an error:
Run-time error 2427 – You entered an expression that has no value.

So I tried coding:
DoCmd.OpenForm "frmPermits", acNormal, , "[Request_No] =" & Forms!frmGrades!Request_No And “Request_No] >” & 0

Then tried.
DoCmd.OpenForm "frmPermits", acNormal, , "[Request_No] =" & Forms!frmGrades!Request_No And Forms!frmGrades!Request_No > & 0

Please forgive me - you can tell I’m no programmer. What am I missing?

Thanks,
SKK

 

[pbaldy]

What is it you want to do if that field is blank?

 

[esskaykay]

Probably just send a message sayng "Permit not available". I was thinking more about this. I was going to try:

If "[Request_No] =" & Forms!frmGrades!Request_No Then

DoCmd.OpenForm "frmPermits", acNormal, , "[Request_No] =" & Forms!frmGrades!Request_No

else
msgBox ""
end if

or something like that. Is this possible or am I out in left field?

SKK

 

[pbaldy]

I think you're on the right track. I would do this:

If Nz(Forms!frmGrades!Request_No, "") = "" Then
  Msgbox "Whatever"
Else
  DoCmd.OpenForm "frmPermits", acNormal, , "[Request_No] =" & Forms!frmGrades!Request_No
End If

 

[esskaykay]

Not quite. I'm still seeing the same error. It's bombing on my form's Unload event.

Private Sub Form_Unload(Cancel As Integer)
DoCmd.SetWarnings False

If Me.Grades = -1 Then
If Me.Request_No > 0 Then
GoTo GoodBye
Else
DoCmd.RepaintObject

DoCmd.RunSQL "INSERT INTO tblGrades ( Permit_No, RequestDate, RequestTime, Location, Contractor, RequestBy, Phone, ReceivedBy, OwnerName, Sidewalk, Driveway ) SELECT tblPermits.Permit_No, tblPermits.PermitDate, txtTime, tblPermits.Par_Addr, tblPermits.Contractor, tblPermits.RequestBy, tblPermits.Phone, tblPermits.ReceivedBy, tblPermits.Ownername, tblPermits.RepairSWK or tblPermits.ReplaceSWK or tblPermits.NewSidewalk, tblPermits.ReplaceApproach or tblPermits.NewApproach FROM (tblPermits INNER JOIN tblLegals ON tblPermits.Notice_ID = tblLegals.Notice_ID) LEFT JOIN tblContractor ON tblPermits.Contractor = tblContractor.Contractor WHERE ((([tblPermits.Permit_No])=[forms]![frmPermits]![Permit_No]))"
DoCmd.OpenForm "frmGrades", , , "[Permit_No] ='" & Forms!frmPermits!Permit_No & "'"

End If
Else
End If

GoodBye:
End Sub


However, I have another idea. I'll keep you posted.

Thanks again,
SKK

 

[esskaykay]

Paul,
I had some additional assistance. Here's what we came up with:

Dim intCountRecs As Integer
intCountRecs = DCount("*", "qryPermits", "[Request_No]= " & Me.Request_No)

If intCountRecs < 1 Then
MsgBox "No Permit available..."

Exit Sub
Else
DoCmd.OpenForm "frmPermits", , , "[Request_No]=" & Me![Request_No]
End If

I hope this doesn't offend you. Thank you very much for your time. Everything bit of assitance I receieve is greatly appreciated.

Again - thanks,
SKK