Open Form with current record (1 Viewer)

GinaWhipp

AWF VIP
Local time
Today, 14:35
Joined
Jun 21, 2011
Messages
5,901
What error message are you getting? For that matter what are you trying to Save before closing frmSearchAppointmentsBetween2DatesBYDOCNAME?
 

asadkhan

Registered User.
Local time
Today, 18:35
Joined
Oct 26, 2019
Messages
52
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.
 

asadkhan

Registered User.
Local time
Today, 18:35
Joined
Oct 26, 2019
Messages
52
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

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, 18:35
Joined
Sep 21, 2011
Messages
14,046
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

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. :)
 

asadkhan

Registered User.
Local time
Today, 18:35
Joined
Oct 26, 2019
Messages
52
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.
 

asadkhan

Registered User.
Local time
Today, 18:35
Joined
Oct 26, 2019
Messages
52
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.
 

GinaWhipp

AWF VIP
Local time
Today, 14:35
Joined
Jun 21, 2011
Messages
5,901
Hmm, AND I see DBGuy is doing quite a bang up job.... moving along now.
 

ddewald

New member
Local time
Today, 14:35
Joined
Jan 31, 2020
Messages
19
Something like...

If field is numeric…
Code:
DoCmd.OpenForm "YourFormName", , , "[NameOfFieldFromDataSource]=" & Me![NameOfFieldOnFormOpeningFrom_ID]

If field is text…
Code:
DoCmd.OpenForm "YourFormName", , , "[NameOfFieldFromDataSource]='" & Me![NameOfFieldOnFormOpeningFrom] & "'"

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, 18:35
Joined
Sep 21, 2011
Messages
14,046
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])
 

ddewald

New member
Local time
Today, 14:35
Joined
Jan 31, 2020
Messages
19
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.
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:35
Joined
Sep 21, 2011
Messages
14,046
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
Code:
Dim strCriteria as String
strCriteria = "[ContactID2]=" & NZ(Me![ContactID],0)
Debug.Print strCriteria
DoCmd.OpenForm "Contact Edit", , , strCriteria

and report back what Debug.Print produces.
 

plog

Banishment Pending
Local time
Today, 13:35
Joined
May 11, 2011
Messages
11,611
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.
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:35
Joined
Sep 21, 2011
Messages
14,046
Hmm, my
Welcome to AWF BTW seemed to go walkabout from my first post in this thread today? :confused:
 

ddewald

New member
Local time
Today, 14:35
Joined
Jan 31, 2020
Messages
19
Nz(Me![ContactID],0) did the trick. I should've caught that. It is numeric so I was thinking the same.

Thanks for the help! Much appreciated.
 

Users who are viewing this thread

Top Bottom