Weird result with NewRecord. Please help (1 Viewer)

[Real Wally]

Hi All,

I have a problem with opening a secondary form from the main form.
Tha main frm is called frmPatents and it’s based on a tabel.
I need to open a secondary form (frmAllOfficialAction_Patents) by clicking a button (bAddOffAction). This secondary form is based on query (qrytblPatents_tblOffActions).

All goes well for patents that already have previous records of an Official Action. The linking field (PublicationNum) is present in both tables and open up fine, a new record can be opened from the form.
However, if this is the first Official Action for a patent I have a problem.
I get the error message: Syntax error (operator missing) in query expression ‘[PublicationNum]= testPatentnumber’

What I need to happen is that the secondary form copies the value in the linking field (PublicationNum) + a few other text fields to matching ones in the new record on frmAllOfficialAction_Patents. I cannot get that done.

With the code I’ve got now I get filled in forms for exicting records and a get blanc forms for new records. Not blanc in the sense that the fields are empty, but blanc in the sense that there’s nothing there, no labels, no field, nothing except the close button in the form footer. I don’t get it! Please advice.

Button:
Private Sub AddOffAction_Click()
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmAllOfficialAction_Patents"

stLinkCriteria = "[PublicationNum]=" & "'" & Me![PublicationNum] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub


Thanks a million,
Wally

 

[Real Wally]

Posted this question a few days ago. Does nobody have a clue as to what it is I'm doing wrong here?

thanks,

Walt

 

[Rakier]

Ok, I'll give it a whirl.

stDocName = "frmAllOfficialAction_Patents"

stLinkCriteria = "[PublicationNum]=" & "'" & Me![PublicationNum] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

The link criteria you are using has to have a valid value for it to open correctly, that is why an existing record opens fine. This really opens the second form in Edit mode, so if there is no PublicationNum, you will get a blank screen as there is not a record there for it to link to.


If you want to add a new record, you have to create a second button with similar code to the other one.

stDocName = "frmAllOfficialAction_Patents"

DoCmd.OpenForm stDocName, , , ,acFormAdd

This will open a blank record and put the form in Add mode. There is no link criteria for this as you are not linking any records.

Hope this helps.

 

[Real Wally]

Thanks for your suggestion.
I've given it a quick try. Unfortunately without the result I'm looking for, the acFormAdd code doesn't open a new record but links to existing ones with the wrong ID. I'll look into it again later and will post a more elaborate analysis.

Wally

 

[Pat Hartman]

You need to save the current record before you open the new form.

DoCmd.RunCommand. acCmdSaveRecord

 

[Real Wally]

Thanks for your responses Rakier and Pat.

After many hours and mountains of frustration I still haven't been able to get it to work I decided to go back a little bit and follow another path to go where I want to go and that turned out to be a good decision. I've got it to work.


Thanks for your input.

Wally