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 Form with current record (1 Viewer)
- Thread starter jamil_786
- Start date
Trevor G
Registered User.
- Local time
- Today, 04:12
- Joined
- Oct 1, 2009
- Messages
- 2,341
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
AWF VIP
- Local time
- Yesterday, 23:12
- Joined
- Jun 21, 2011
- Messages
- 5,900
Something like...
If field is numeric…
If field is text…
If field is numeric…
Code:
DoCmd.OpenForm "YourFormName", , , "[NameOfFieldFromDataSource]=" & Me![NameOfFieldOnFormOpeningFrom_ID]If field is text…
Code:
DoCmd.OpenForm "YourFormName", , , "[NameOfFieldFromDataSource]='" & Me![NameOfFieldOnFormOpeningFrom] & "'"
.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 :-(((
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 :-(((
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
What I want is the form to open up to record (MOSID) number 5
NauticalGent
Ignore List Poster Boy
- Local time
- Yesterday, 23:12
- Joined
- Apr 27, 2015
- Messages
- 6,319
Holy Flash-Back, BatMan!
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?
Last edited:
GinaWhipp
AWF VIP
- Local time
- Yesterday, 23:12
- Joined
- Jun 21, 2011
- Messages
- 5,900
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?
So, is the MOSID always 5 or does it change based on who logs on?
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
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
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.
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.
