Open Form with current record

[jamil_786]

Dear Friends, I need some help. I have a Database with 4 forms with different fields but one field is common in all forms "Civil ID/Passport". I want to go the specific record of the another form with the current open record of the form with the help of command button.

 

[Trevor G]

Dear Friends, I need some help. I have a Database with 4 forms with different fields but one field is common in all forms "Civil ID/Passport". I want to go the specific record of the another form with the current open record of the form with the help of command button.

Open the first form in design view, then press F11 and select to Create a New MACRO, then select to Open Form from the actions, then select your second form. Then in the parts below select the form name, then below in the Where Conditions, use the Elipsee (...) button the right to select the Table then the Civil ID field = the form (which is open in design view) and the Civil ID field.

Then save the macro. Give it a name something like mcrOpenFormName, then close the macro. Then drag the macro into your form this will create a button for you, save the form change the view and click the button after you have selected a record.

 

[GinaWhipp]

Something like...

If field is numeric…

DoCmd.OpenForm "YourFormName", , , "[NameOfFieldFromDataSource]=" & Me![NameOfFieldOnFormOpeningFrom_ID]

If field is text…

DoCmd.OpenForm "YourFormName", , , "[NameOfFieldFromDataSource]='" & Me![NameOfFieldOnFormOpeningFrom] & "'"

 

[kemas]

very good
thank you

 

[|-|olmes]

Gina Whipp
thats really nice work thank you .

 

[alanquick]

Can't get this to work :-(

Here is my basic code:

Private Sub btnlogin_Click()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("tblemployee", dbOpenSnapshot, dbReadOnly)
rs.FindFirst "SAPID='" & Me.txtSAPID & "'"


If rs.NoMatch = True Then
MsgBox "You are Not Authorised to Access This Database", vbCritical
Exit Sub
End If


DoCmd.OpenForm "frmMOS_main"
DoCmd.Close acForm, Me.Name



End Sub

The basic code works fine to open the correct form but it always open as a blank/new record, what I want to happen is for the form to open to a specific record (MOSID 5)

My Primary Key is the MOSID but no matter what code I write the form doesn't open from

I must be so thick :-(((

 

[GinaWhipp]

Looks like that field is numeric, try...

rs.FindFirst "SAPID=" & Me.txtSAPID

 

[alanquick]

No the SAPID field is in fact text (Some people have names) The MOSID (which is what I'm looking to find) is numeric. SAPID is purely for logging in

 

[GinaWhipp]

Okay, so I just read the ENTIRE message, why not just do...

DoCmd.OpenForm "frmMOS_main", , , "SAPID='" & Me.txtSAPID & "'"
DoCmd.Close acForm, Me.Name

Not sure why the FindFirst is in there at all.

 

[alanquick]

The form opens fine with the existing code ( Guess I didn't explain myself properly) When the form opens I want it to go to a specific MOSID (not a SAPID) but it always opens to a new record

What I want is the form to open up to record (MOSID) number 5

 

[GinaWhipp]

But your code is listing SAPID, where is it supposed to get the MOSID? That said if you always want it to open to number 5 then add that to criteria for the arguments for opening the Form.

 

[NauticalGent]

Holy Flash-Back, BatMan!

 

[alanquick]

The confusion here is that this code is for the login screen. The login takes the SAPID from the employee table and matches it against the computer ID with environ ("username") then when the database opens it opens the main form. When the main form opens I want it to go to a record defined by the MOSID. The SAPID isn't needed for that it is the argument for going straight to a specific record that I can't seem to get right. I would have thought that where I have the command DoCmd.Openform "frmMOS_main" I would have put the code in there to go to the specific record or does the code need to be in the frmMOS_main form?

 

[GinaWhipp]

Okay, I think you're going to want to use a Public Variable to get what you want. You can assign it at the log on form and then use it as a parameter to open the next Form thereby opening a specific record or Recordset.

So, is the MOSID always 5 or does it change based on who logs on?

 

[alanquick]

Yes I want the form to always open to MOSID 5 for everyone and not be specific to any individual user

 

[GinaWhipp]

Oh, then no public variable, the below should work...

DoCmd.OpenForm "frmMOS_main", , , "[MOSID]=" & 5"
DoCmd.Close acForm, Me.Name

 

[alanquick]

Nope :-( tried that a few times last night, still opens up the main form to a new record. :-(((

 

[alanquick]

this is the code that is in the main form (frmMOS-main)

Option Compare Database
Option Explicit
Dim intCurrent As Integer
Dim intPrevious As Integer
Private Sub TabCtl0_Change()
If IsNull(Me.Title) Then
MsgBox "Title cannot be empty", vbExclamation
Me.Title.SetFocus
End If
End Sub

Private Sub FastKey_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = vbKeyReturn Then
Me.RecordSource = "SELECT tblMOS_main.* from tblMOS_main where (((tblMOS_main.MOSID)=forms!frmMOS_main![fastkey]));"
Me.FastKey = ""
intCurrent = Me.MOSID
End If
End Sub

Not sure if this is interfering with it

 

[GinaWhipp]

First I would make sure you don't have the Form set to open for Data Entry Only. If that is not it then try remming that out and see what happens.

 

[asadkhan]

What if need to combine date and text parameters together to pass to opening form to display records based on text valeu between two dates.
I have following problem:

I am trying combine following two fields in ms access vba,

Private Sub Command6_Click()

DoCmd.OpenForm "frmRecordbetweenTwoDatesbyDoctorNameFormView" , , , "dtDate between #" & Format([ApptStartDate], "dd-mmm-yyyy") & "# And #" & Format([ApptEndDate], "dd-mmm-yyyy") & "# And [txtConsultantDoctorName] = '" & Me.CBODocName & "'"

DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close acForm, "frmSearchAppointmentsBetween2DatesBYDOCNAME", acSaveYes
End Sub
----------
I am actually trying to pass parameters from a form which contains start and end date and a combo box containing names. what actually I am trying is looking up doctors' appointments between two dates with their names (doctor name). I am able to search them separately.