What to use in this instance

[RyanB]

Hi,

I have a form(frmentry) where a user creates a record and it has a primary key that is entered manually the primary field is called "AR_Number". The user enters the details in this form and then click a button called "butapprove" and it launches another form called 'frmentryapproval'. When this second form loads up i need it to be on the record that the user was just entering, wondering what the best way is to load that record into the form.

currently I am using this code:
Private Sub butapproval_Click()
Dim appformname As String
Dim apparnumber As String

appformname = "frmentryapproval"
apparnumber = "[AR_Number]=" & Me![ARNo]
DoCmd.OpenForm appformname, , , apparnumber
End Sub

but when using that it does not work properly and the values entered on the 2nd form are not saved at all, let alone to the required record.

I have seen some things on GoToRecord & FindRecord but don't know how to use them, would the be useful in this situation?

Thanks for your help and sorry for the n00b question.

Ryan

 

[WayneRyan]

Ryan,

First of all, you are launching the second form properly.

I take it that the 2nd form isn't intended to show any information from the
first form. There probably isn't any visual indication that it is on the
correct record.

Assuming that Form1 displays information from Table1, Form2 displays information
from Table2, and that you don't have any problems with that, then there are a
couple of things that you can do.

1) Make the information for Table2 a subform of Form1. You can use define the
Master-Child links and let Access keep your tables in synch.

2) You can launch Form2 (as in your earlier example), but make sure that Form2
is actually BOUND to Table2. If it isn't, you will get the symptoms that you
describe. Also, Table2 is going to have to "relate" to Table1, so on the key
field(s), set the DefaultValue to Forms![Form1]![SomeKeyField]! Very important
to keep them in "synch".

hth,
Wayne

 

[RyanB]

Thanks for the reply.

Your assumptions are correct except that form2 does write to table 1

Ryan

 

[WayneRyan]

Ryan,

In that case, Form1 already has the record in the Edit Process.

Form2 would have to "push" the values back when an "OK" button
was pushed.

Forms![Form1]![SomeField] = Me.SomeField
Forms![Form1]![SomeOtherField] = Me.SomeOtherField

Why does the data have to be on a second form?

Wayne

 

[RyanB]

Hi,

I have now changed it so the data on form 2 has its own table

so basically all i need to do is take a refernence from form1 (the primary key from table 1) and use that reference to load a record on form2 the reference from form1 is also the primary key in table 2.

sorry if this doesn't make sense, having a brain melt at the moment

 

[WayneRyan]

Ryan,

Base Form2 either on Table2, or on a query that queries all of Table2.

Launch Form2 using your original syntax.

On the Primary Key field of Form2, set the DefaultValue to:

Forms![Form1]![ThatKeyField]

Then the two forms/tables will stay in synch.

hth,
Wayne

 

[RyanB]

Still problems

Thanks,

Still having problems when i try open a specific record with a button tho, my code is below:

Secuser = ("[username]=" & fOSUserName())
DoCmd.OpenForm frmSecSettings, , , Secuser

The error is 'the action or method requires a form name argument...

Driving me up the wall

Cheers,

Ryan

 

[Sergeant]

Secuser = ("[username]=" & fOSUserName())
DoCmd.OpenForm frmSecSettings, , , Secuser

The error is 'the action or method requires a form name argument...

In the first line, fOSUsername returns a string, no? So wrap it in single quotes: (don't think you need the parentheses, either.)
Secuser = "[username]='" & fOSUserName() & "'"
In the second line, shouldn't the form name be in quotes?
DoCmd.OpenForm "frmSecSettings", , , Secuser

 

[RyanB]

OMG that works, i am forever in your debt

 

[Sergeant]

One of the few occasions where I actually had the right answer. Glad to be of assistance.
Confuscius say: You teach best, that which you most need to learn.
Also: Not learning, only realizing that which you already know. (Not Confuscius)

 

[RyanB]

GRRR *pulls hair out*

Ok, when I apply the same code structure to another form it doesn't work for me, it gives me the error that 'The OpenForm Action was cancelled'.

here is my code:
Private Sub butapprove_Click()
On Error GoTo Err_butapprove_Click

arnum = ARNo.Value
stARfind = "[ARNumber]='" & arnum & "'"
MsgBox stARfind
DoCmd.OpenForm "frmApproval", , , stARfind
Exit_butapprove_Click:
Exit Sub

Err_butapprove_Click:
MsgBox Err.Description
Resume Exit_butapprove_Click

End Sub


Please help

 

[Sergeant]

Yeah, if it's a numeric data type, then you don't wrap it in single quotes...
stARfind = "[ARNumber]='" & arnum & "'"
should be...
stARfind = "[ARNumber]=" & arnum