DoCmd.GoToRecord , , acNewRec (1 Viewer)

[popeye]

i have a listbox that manipulates records on another form but in case i click on one of the records in the listbox that doesnt have any corresponding record in the other form then instead of it saying that it cant locate the record i would like the form to go to a new record....

i've been trying at it but cant get it right...

plz assist... anyone.

 

[ansentry]

Post the code you are using.

 

[popeye]

The Code

Private Sub lstCalValue_AfterUpdate()
Forms!frmMain!frmS.Form.RecordsetClone.FindFirst "[checknum] = " & Me![lstCALVALUE] & ""
If Not Forms!frmMain!frmS.Form.RecordsetClone.NoMatch Then
Forms!frmMain!frmS.Form.Bookmark = Forms!frmMain!frmS.Form.RecordsetClone.Bookmark
Else
MsgBox "Could not locate [" & Me![lstCALVALUE] & "]"
End If

End Sub



in the else section is where i would like to put the code. the lstCalValue listbox is in one subform window and the other form that it manipulates is in another subform window....
So the other form would be refered to as forms!frmMain!sfmG.form! etc
if i put the new record button on the form that should go to the new record then it wouldnt be a problem cause the wizard would just write the code, but i dont want to put the button on that form, i just want if the user clicks a record in the listbox it does one of two things

1. Find the record in the recordset and send the other subform to it
or
2. Set the subform to a state where it will take new records.

 

[Banana]

You're doing it backward, I think. In order to add new records to subform, you have to be at new record on the parent form. Otherwise you're just editing a existing record.

If there's already a parent record, then subform will automatically link itself with the parent record and be ready to accept new records. It will not be available if there is no parent record, assuming you have RI enforced (and you should).

Do you still want to create a new parent record if there's none found in the listbox?

 

[popeye]

okay i have one main form... and two SUBFORM "WINDOWS" on the form...
one subform window has a form in it that "acts" like a remote control for all that is done on the other form that is currently active in the other sbform window

so frmS is the subform window name that has in frmCalSEt which is the remote

and frmH is the subform window name that has in frmPM

Frmcalset is the one that has on the listbox with a list of records...
frmPM is the one that should display records if any that correspond to the record selected in the listbox in frmCalset

now if a record is selected in frmCalSet bt doesnt have a PM record corresponding to it in the tblPM which is the source for frmPM then it should send frmPM into a state ready to take a new data entry....

hope i made some sense in that explanation....

 

[Banana]

So they're a sibling subforms then.

Now, are they both related to records on the main form? If that is the case, then the record on main from needs to be "created" in the frmH before you can add records in frmH.

If they are not linked to the main form, then it's up to you to tell the frmH which record you want to move to.

Perhaps if you post your db, or give more details on how your mainform/subforms are linked, we can give more specific examples.

 

[popeye]

okay really strapped for time and the db is large...

the mainform does not have a recordsource


okay...

on the sbform if i created a new button it would have the codes
DoCmd.GoToRecord , , acNewRec


can i modify this to be executed from another form....?

if i was creating the same add new record button but placing it on another form how would i get it to execute the action on the other form?

 

[Banana]

I think I did the same thing, but I don't have Access with me so can't check my code;

I did something like:

With Forms!frmSomeForm
     DoCmd.RunCommand acCmdMoveRecordsGoToNew
End With

The idea is that you have to make sure you're talking about that form, not the one you're in right now.

HTH.

 

[popeye]

Gona fool around a bit with that and see if it turns up... thanx for the help so far though...

 

[popeye]

you do have a clear idea of what i want to do


tried the code and got "run time error '2105'
you cant go to the specified record....

i feel lost.

 

[Banana]

I'll be in work later the day, then I can check my code against one I proposed above.

Also, are you sure there's nothing holding back the forms from moving to new records. That is, can you manually move to a new record at same time as you try the code?

 

[popeye]

well there isnt anything that restricts them... ive added a newbttn on the form i want to go to the new record and it works...

well i'll just wait till you get around to access your codes... by the way.. thanx alot for the help so far.

 

[Banana]

I was wrong. It's the setfocus that does the trick. Here's my code that I use to get my subform advance to a new record when I'm within the parent form. Since you'd be calling from a sibling subform, you may need to add line to set focus back on the parent then move to the subform.

Me.frmGuestInfoRef.Form!cmbVisitTypeID.SetFocus
        If Not Me.frmGuestInfoRef.Form.NewRecord Then
            DoCmd.RunCommand acCmdRecordsGoToNew
        End If

HTH.

 

[popeye]

run time error "2046" the command or action 'recordsgotonew' isnt available now.

Forms!frmMain!sfmG.SetFocus
If Not Forms!frmMain!sfmG.SetFocus Then
DoCmd.RunCommand acCmdRecordsGoToNew
End If

 

[Banana]

1) You're trying to set focus on your subform, then saying that if it doesn't have the focus, to move to new record. That won't work.

2) If that code is inside a sibling form, you need to make sure that you first give your parent form the focus, then move on to the subform, then move on to subform's control, then only you can issue new record to that subform.

HTH.

 

[popeye]

maybe i need a different approach cause this is just not working... and i cant see why it isnt. I guess it wouldnt be fun if it wasnt a bit frustrating, especially tripping over the seemingly "simpler" stuff...

really need to get this done though.

any more ideas?

 

[Banana]

It should work.

In fact, I whipped up a database. It has two subforms that are *NOT* related to the Parent or even each other. Both subforms has a command button that will move the another subform to a new record.

HTH.

 

[popeye]

okay this is really good...

note though that im using the subform windows to call multiple forms so i need it to work without having to make direct reference to any particular "object" on the form

'Forms!frmParent.Form!frmSister!txtSister.SetFocus

I commented out that line in your code to see if it would work but it ran me an error...


if i can get it working generally without using that line then my aim would be met, otherwise im gona have to put a common control on all of these forms and refer to it, which i really dont wana do...

there should be a way though, no?

 

[Banana]

In fact, there is a workaround. I have two or three subforms that only has one control that needs to be enabled/disabled depending on circumstances. I put in a textbox of zero width, zero height, and call it "txtSetFocus" and use that to set focus so I can disable/enable the subform's control without my users being any wiser.

But if you have more than just a handful, I'd probably look into using a function to retrieve the subform's name, subform's control's name. You do need to have a focus on one of subform's control in order to do stuff. I can't explain why simply focusing on subform won't work. I'm just parroting MVPs here...

HTH.

 

[popeye]

Well I may as well embrace the solution that has been presented... I coulda sworn I saw something similar to what i wana do once while browsing the forum that didnt require setting focus....

I have added a text box to all the forms that I would need to use... In addition, ive added a recordsource to the main form, one is needed to apply your method, so All in all I guess this will work until it wont and Im not foreseeing when it wont work so MANY THANKS, THINK YOU EARNED YOUR SALARY FOR THE LAST COUPLE OF DAYS... HOPE I CAN EARN MINE NOW