Still trying to link two forms together

[thisisntwally]

In trying to link between two forms im having some problems (no doubt due to the primary key being an autonumber, in addition to my tpying slliks)

this:

Dim stDocName As String
Dim stLinkCriteria As String
 
stDocName = "Update"
 
stLinkCriteria = "![keyID]=" & Me!txtkey
DoCmd.openForm stDocName, , , stLinkCriteria

gives me a 'syntax error'

this:

DoCmd.openForm "Update", acNormal, , "!keyID='" & Me![txtkey] & "'", acFormEdit, acWindowNormal

prompts me for a parameter value for keyID

this:


D

im stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "Update"
    stLinkCriteria = "[keyID]=" & "'" & Me![txtkey] & "'"
 
    With DoCmd
        .RunCommand acCmdSaveRecord
        .openForm stDocName, , , stLinkCriteria
    End With
 
    Forms![Update]![txtkey].DefaultValue = Me![txtkey]

says command or action saverecord is unavailable.

when put into this:

Private Sub Command85_Click()
 
On Error GoTo Err_Command85_Click
 
    With CurrentDb.OpenRecordset("tbl Modifications", dbOpenTable)
        .AddNew
          !Program = Me.txtProgram                         '\
          !Name = Me.cboName                                 '|
          !ObjectClass = Me.cboObjectClass
          !ParentProgram = Me.txtParentProgram
          !Fund = Me.txtfund
          !ReportingEntity = Me.txtReportingEntity
          !PurchaseOrder = Me.txtPurchaseOrder
          !Comments = Me.txtComments
          !AppropriationYear = Me.cboAppropriationYear
          !Site = Me.txtsite
          !DOR = Me.txtDOR
          !FiscalYear = Me.cboFiscalYear                            '|
          !Description = Me.txtdescription                           '|
          !Budget = Me.txtCharge                             '|
          !UpdateDate = Date
          !SSD = Me.txtSSD
          !CertifiedDocument = Me.objRequest
          !BCD = Me.txtBCD
          !FCD = Me.txtFCD
          !Vendor = Me.txtVendor
          !Amendment = Me.txtAmendment
          
        .Update
        .Bookmark = .LastModified
         
   End With  
  
Exit_Command85_Click:
    Exit Sub
 
Err_Command85_Click:
    MsgBox Err.Description
    Resume Exit_Command85_Click
    
End Sub

 

[pbaldy]

I think your first effort is basically correct, but what is the exclamation point doing in there?

 

[thisisntwally]

i think i threw that in there trying to get rid of the syntax error, but no luck. when i take it away it still returns the same error. Someone somewhere told me that needed to be directly referencing the underlying form, but assuming that it isn't(keyID is the field name, on the table) im not sure how to make it 'is'.

ok this:

 Dim stDocName As String
  Dim stLinkCriteria As String
 
    stDocName = "Update"
     stLinkCriteria = "!keyID=" & "'" & Me![txtkey] & "'"
 DoCmd.openForm stDocName, , , stLinkCriteria

prompts me for a parameter value

 

[pbaldy]

You get an error with this?

stLinkCriteria = "[keyID]=" & Me.txtkey

 

[thisisntwally]

You get an error with this?

stLinkCriteria = "[keyID]=" & Me.txtkey

i sure do

stepping through it seems to limit my linkcrit to "[keyID]="

 

[boblarson]

i sure do

Then I would guess you need quotes:

stLinkCriteria = "[keyID]='" & Me.txtkey & "'"

as your keyID would probably be text, eh?

 

[thisisntwally]

Then I would guess you need quotes:

stLinkCriteria = "[keyID]='" & Me.txtkey & "'"

as your keyID would probably be text, eh?

keyID is autonumber

the extra quotes cancel the openform action.

**gotta go to a meeting - back in 60**

 

[boblarson]

Then make absolutely sure that your text box is named txtKey and that you didn't just think that it was, because I cannot see why it would not work but you could try this one:

stLinkCriteria = "[keyID]=" & Me!keyID (which references the currently active FIELD of the recordset and not the text box)

 

[pbaldy]

Is this a new record, where the autonumber might not be assigned yet?

 

[boblarson]

Is this a new record, where the autonumber might not be assigned yet?

That's about the only thing left, isn't it Paul? If all of the names are correct.

 

[thisisntwally]

it is a new record ( i had the sub up, but i know thats a lot to read) , that being said i cant get With DoCmd
.RunCommand acCmdSaveRecord
.openForm stDocName, , , stLinkCriteria

to work either (see post 1)

 

[boblarson]

If it is a new record on the form where you are clicking to open the other form, then there isn't anything to link them together as the original form doesn't have a record. So, it would be pointless to use linking criteria at that point. If I am understanding the sequence of events correctly.

 

[thisisntwally]

im sure you are understanding better than I. and im glad (kinda) to hear that definitively. What can I do to open the newly made record in another form?

/the context here is that *sometimes* the information entered from form2 will be available immediately (instead of months later) and I would like the option to enter said information to the record.

*edit* my only other lead was the findrecord option which it seems to me will have the same problem.
*can i use the .Bookmark = .LastModified to call back up the record? i worry the answer is no, because as i understand it bookmarks vanish with the forms close (whats the point then!)
*you dont really have to answer these but why doesn't .RunCommand acCmdSaveRecord save the record?!(besides that i cant get it to work)
*or if it does save(provided i could get it to work) why cant i reference the record?!?

 

[boblarson]

I think you need to figure out whether there's a record for the keyID in the other table yet. Then, call your form, either WITH the where clause if the key exists, or without (and setting the keyID into the opening form) with code if it doesn't. You should be able to use a DCount for that.

 

[thisisntwally]

 Dim stDocName As String
    Dim stLinkCriteria As String
 
     stDocName = "Update"
     [B][COLOR="Red"]stLinkCriteria = "[keyID]=" & Me!txtkey[/COLOR][/B] '<==WE HAVE A RECORD
     DoCmd.openForm stDocName, , , stLinkCriteria

I checked the history table just before the open form command and there is indeed a record. it made me feel warm and fuzzy just to know it was there.
ahem.
Let me see if i understand whats going on then: As my form is (edit:build =>buildinated-ed) to the purpose of creating new records it is never displaying a record to link to, only creating one and dumping in field values. As such if i want a form to open the newly created record, it must somehow draw its values (or at least open the record) direct from the table.

Would it be at all plausible to have form1 add a blank record after it updates, which could be opened up by form1's of future and edited for the purpose of data entry. in other words the present form1 edits the new record made by the past form1 (marked by a new/blank record entry, or some autogeneratedkey). as form1 is now opening an existing record linking criteria should suffice to open Form2. and last form1 creates another blank record.

 

[boblarson]

Any possibility you can post your database here so we can take a look and maybe play with it a bit? I'm way more visual and I think we could nip this in the bud a bit quicker by doing so, if you are so inclined. I think I'm to the point right now that I'm still a bit confused as to what, exactly, we are dealing with and I'll bet that about 5-20 minutes of having it in front of me we can take care of this one and move on. Of course, many times when I've said that, I find that when I look at it the design is such that it really needs a major overhaul to work properly, so the person gets more than they bargained for when I return the verdict , but if you want to live dangerously then ...

 

[thisisntwally]

Ive actually just solved the linking problem. You were spot on about the problem being that a form which makes new records does not have one open in it.

If i need serious help again i'd be glad to throw it up.

i'm pretty enthusiastic about this past and future form idea and as i like to fight a war on as my fronts as possible am running with it.
SO
this:

With CurrentDb.OpenRecordset("tbl Modifications", dbOpenTable)
        .Edit
             !EntryStatus = 1
        .Update

opens a table to edit. this dumps the record ive finally managed to open in the form, and uses the first.

as i now have the form already open to the record in question, how to i set a command to edit and update?

 

[boblarson]

Is there any reason why you can't just have the form be bound? Then, you can just disable and lock the fields until you want to unlock them. Or you can conditionally lock them when you open and there's an existing record and then when it is new it isn't locked.

 

[thisisntwally]

I forgot i bound the form. that means something like this:

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

would work? (stolen from gandalf)

if a simple command will do it, can i still specify a field i want changed to a specific value? ie change entry status from 0 to 1?

*im remembering, bound forms automatically update on close right? so could i really just open form2 and form1 would save on close?

 

[Rich]

You shouldn't have bothered stealing the code from gandalf, it's been obsoletee since 1997