Open Form with current record
- Thread starter jamil_786
- Start date
Gasman
Enthusiastic Amateur
- Local time
- Today, 03:18
- Joined
- Sep 21, 2011
- Messages
- 16,612
Gina,
The o/p has posted 3 seperate posts on this in different subforums, Reports even. :banghead:
He is currently getting help from the DBguy in https://www.access-programmers.co.uk/forums/showthread.php?t=307582
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.
I don't think its illegal to seek help from other people. The reason why I did because somehow I am not able to convey my problem to other person, I am not saying he is not able what I am saying, it is my fault. You can ignore everything I have requested as you are the owner, seems to me... And also beggers are not choosers.... Usually they knock all doors and hope someone might be able to help them....
Good luck to you
Gasman
Enthusiastic Amateur
- Local time
- Today, 03:18
- Joined
- Sep 21, 2011
- Messages
- 16,612
It is not iilegal to seek help from other people, but it is common courtesy to say that you are doing so. Most people post on other sites and do not mention it.
Then the help is being duplicated and people who give their time freely to help others are wasting their efforts as others have already solved the problem for them.
the DBguy knows exactly what you are trying to do as I think I do.
Stick to one thread and then everyone can see what has been tried, which is the reason I am mentioning it to Gina, as no doubt she would offer the same advice as the DBguy and anyone else who chips in.
Have you tried creating a string for the where criteria and debug.printed that as I suggested.?
Post back in that thread with the result, NOT this one.
No I am not the owner, just another user trying to stop others wasting their time offering advice that might well have already been offered in another thread.
You are not doing yourself any favours with the shotgun approach. Pick a thread and stick with it to get the best results.
That is my 5 pence.
I am not sure what part of my request is confusing.
Let explain to you again
I have two forms
Form one is used to pass following parameters to form two:
Consultant/Doctor Name (it's a combo box)
Appointment start date
Appointment end date (both dates are from date of appointment as you may see Between and And are use)
The form one has got two buttons, one to run the code in question and other is to close the form one.
Now the second form contains doctors name and dtdate(which actually is appointment date) and sundry others fields.
The form two is base on a simple select query no criteria attached. Form two opens from form one with parameters passed to it to show only records which meet the criteria.
Furthermore, I am able to pass criteria when I only use dates. Form two shows data as required. Same in case of doctor consultation name, if I use it on its own it works fine.
Problem arises when I want to pass both parameters at the same time.
As far as attaching dB I can't do that because of nature of dB. This might put you off, it's okay to me if you can't help in this case. I am really thankful to you for kind help.
Let explain to you again
I have two forms
Form one is used to pass following parameters to form two:
Consultant/Doctor Name (it's a combo box)
Appointment start date
Appointment end date (both dates are from date of appointment as you may see Between and And are use)
The form one has got two buttons, one to run the code in question and other is to close the form one.
Now the second form contains doctors name and dtdate(which actually is appointment date) and sundry others fields.
The form two is base on a simple select query no criteria attached. Form two opens from form one with parameters passed to it to show only records which meet the criteria.
Furthermore, I am able to pass criteria when I only use dates. Form two shows data as required. Same in case of doctor consultation name, if I use it on its own it works fine.
Problem arises when I want to pass both parameters at the same time.
As far as attaching dB I can't do that because of nature of dB. This might put you off, it's okay to me if you can't help in this case. I am really thankful to you for kind help.
It is not iilegal to seek help from other people, but it is common courtesy to say that you are doing so. Most people post on other sites and do not mention it.
Then the help is being duplicated and people who give their time freely to help others are wasting their efforts as others have already solved the problem for them.
the DBguy knows exactly what you are trying to do as I think I do.
Stick to one thread and then everyone can see what has been tried, which is the reason I am mentioning it to Gina, as no doubt she would offer the same advice as the DBguy and anyone else who chips in.
Have you tried creating a string for the where criteria and debug.printed that as I suggested.?
Post back in that thread with the result, NOT this one.
No I am not the owner, just another user trying to stop others wasting their time offering advice that might well have already been offered in another thread.
You are not doing yourself any favours with the shotgun approach. Pick a thread and stick with it to get the best results.
That is my 5 pence.
You are right I better go away.
NauticalGent
Ignore List Poster Boy
- Local time
- Yesterday, 22:18
- Joined
- Apr 27, 2015
- Messages
- 6,750
That is not want Gasman or anyone else wants. There is nothing wrong with cross-posting as long as you do it properly.
Give this post a read: https://www.excelguru.ca/content.php?184
Gasman
Enthusiastic Amateur
- Local time
- Today, 03:18
- Joined
- Sep 21, 2011
- Messages
- 16,612
That is not what I meant at all, and I think you know it.
Use this thread https://www.access-programmers.co.uk/forums/showthread.php?t=307582&page=2 to post all your queries about this particular problem.
If you have a new problem, start a new thread for that.
This worked like a charm for me! Is there something I can add to the code to ignore blank fields? I have this set up in a datasheet as a hyperlink to edit a record, but I would also like to use the same hyperlink to create a new record and I keep getting the error:
Syntax error (missing operator) in query expression '[ContactID2]='.
I have the code written as follows:
DoCmd.OpenForm "Contact Edit", , , "[ContactID2]=" & Me![ContactID]
Thank you in advance!
Gasman
Enthusiastic Amateur
- Local time
- Today, 03:18
- Joined
- Sep 21, 2011
- Messages
- 16,612
Damn, this just the thread that keeps on giving. Started in 2011 and still being resurrected.
You would have been better off starting your own thread, as that would likely get a better response than an old thread?
Anyway you could try using the NZ function?
NZ(Me![ContactID])
Started in 2011 and still being resurrected. You would have been better off starting your own thread, as that would likely get a better response than an old thread?
Anyway you could try using the NZ function?
NZ(Me![ContactID])
I thought about starting my own, but I wanted to give credit to who gave me my answer thought this might be the best place to do that.
I tried DoCmd.OpenForm "Contact Edit", , , "[ContactID2]=" & Nz(Me![ContactID])
Got the same error.
thought this might be the best place to do that.I tried DoCmd.OpenForm "Contact Edit", , , "[ContactID2]=" & Nz(Me![ContactID])
Got the same error.
Gasman
Enthusiastic Amateur
- Local time
- Today, 03:18
- Joined
- Sep 21, 2011
- Messages
- 16,612
I was assuming that ContactID was numeric?, is that the case.?
You could also try Nz(Me![ContactID],0) but I thought it defaulted to zero if the value being checked was numeric ?
Another thing to try is
and report back what Debug.Print produces.
You could also try Nz(Me![ContactID],0) but I thought it defaulted to zero if the value being checked was numeric ?
Another thing to try is
Code:
Dim strCriteria as String
strCriteria = "[ContactID2]=" & NZ(Me![ContactID],0)
Debug.Print strCriteria
DoCmd.OpenForm "Contact Edit", , , strCriteriaand report back what Debug.Print produces.
Jesus was only resurrected once (and only after being dead for 3 days). This thing has been dead for over 2 years three times (2011-2013, 2013-2017, 2017-2019) and brought back to life. So to be fair to ddewald, 3 months to this thread is nothing.
