Save referenced record and goto duplicate record?

[Kila]

I have an appointments form (that is actually a subform of another form) that, upon entry of a patient name that is not in the database (there is also a combo-box to chose), prompts you to double-click the field to enter the data. When you double-click, you open another form that allows you to enter the patient info (name, SSN, etc.) How can I automatically have this field select the patient name that was entered when the patient info box is closed?

Also, how can I get the patient info form to automatically go to the appropriate record if a social security number is typed in that has already been used, rather than just give an error?

Thanks for your help!

 

[dan-cat]

Kila,

You can email me a copy of your db if you want - so I can take a look

 

[Kila]

How do I send it?

I would love to send it to you, but I cannot figure out how to do this without an address. The "send email" and "send private message" options do not have an attachment options. A reply here is too large, even though the e-mail is zipped, and pretty minimal.

Please let me know what I should do! Thanks for your offer of assistance.

 

[dan-cat]

Why don't you sign up for a yahoo email account?

www.yahoo.com

My email address is dan_cat@btopenworld.com

 

[Kila]

No probs with e-mail addresses!

Actually, I have a free one on Excite, a paid one on Roadrunner, and one at work. I've got e-mail addresses I just let die b/c I couldn't keep checking them all! (Darn Spam!) I just needed one to contact YOU. Thank you SO MUCH for helping me! I will send it to you now.

BTW, please also look at my other post about Subforms, if you are able. If you could provide a two-fer, I would be eternally grateful, so would the staff who are waiting on me to finish these!

 

[Kila]

Pinky?

By the way, if Pinky's got your back, should I be concerned about your plots to take over the world?

 

[Kila]

I have been trying to reach you!

Just as an FYI...I have been trying to respond to your e-mails, but our organization's firewall keeps holding it back. Apparantly you can get in, but I can't get out to that provider's addresses. The delay is due to the fact that it does not reject it until about midnite! At any rate, I have sent a response via my home e-mail. Hopefully you will receive that. Please respond to both addresses. Thanks.

 

[dan-cat]

Re: I have been trying to reach you!

Just as an FYI...I have been trying to respond to your e-mails, but our organization's firewall keeps holding it back. Apparantly you can get in, but I can't get out to that provider's addresses. The delay is due to the fact that it does not reject it until about midnite! At any rate, I have sent a response via my home e-mail. Hopefully you will receive that. Please respond to both addresses. Thanks.

Just to let you know I haven't received anything as yet - I will reply once I do

 

[Kila]

Strange!

Very strange. I sent out the response before I sent this post via Road Runner a few hours ago. Maybe there is a delay overseas? I sent it to the address above...dan_cat@btopenworld.com. Maybe there is something about btopenworld.com firewalls don't like? Or mabe btopenworld.com rejects large attachments? Hmm. I hope it arrives soon...my boss is leaning on me to fix the survey one! Any suggestions?

 

[dan-cat]

I got your other db ok - back on the 20th Jan that was sent from your *@med.va.gov address. My email address doesn't have a size limit on it either. Can you strip the db down and zip up so it will fit on a forum post?

 

[Kila]

Tried that first!

I tried. It's still too big. Even stripped down & zipped it is 2.14 MB. Do you have another address? A Yahoo one like you suggested maybe? The error message says it does not like you or anyone in your domain. Unfriendly thing, that firewall! However, I sent the RoadRunner one earlier today (rr.com). I was able to send it to that address & forward it on. I am suprised you have not received that one yet. As far as the .gov address, our IP address depends on which server we logon to, but we cannot control it, it's automatic. I get the errors timestamped around midnite, even though I sent it around 9am. That is probably how you got the first DB, I had a different server.

 

[dan-cat]

try dan_cat1793@yahoo.com

 

[Kila]

In the meantime....

In the meantime, regarding the survey question, this is what I am struggling with at the moment:

Private Sub cboAnswerID_Enter()

End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
cboAnswerID.Requery
End Sub
Private Sub cmdNextAnswer_Click()
On Error GoTo Err_cmdNextAnswer_Click

DoCmd.GoToRecord , , acNewRec

Exit_cmdNextAnswer_Click:
Exit Sub

Err_cmdNextAnswer_Click:
MsgBox Err.Description
Resume Exit_cmdNextAnswer_Click

End Sub


This is the code behind the SurveyAnswer subform. As you can see, it requerys BeforeInsert. However, this makes you have to click it twice to display ffresh combobox items. I cannot seem to get it to work when I put it in other sections (On click, etc.)

For the second question, Where can I put this DoCmd.GoToRecord on the other subform so that the two forms will advance at the same time...and maybe refresh the combo box while they are at it? I have tried it before, but I may have gotten the syntax wrong. Also, the other subform is actually a sub-sub form. Any thoughts?

This is the code behind the sub-sub form ActiveQuestion. As you can see from the many "REMs" I have tried many different ways to get it to interact with the subform ActiveAnswer.

Option Compare Database
Option Explicit

Sub Form_Current()

Dim ParentDocName As String
Rem Dim ctlActiveAnswer As Control
Rem Set ctlActiveAnswer = Forms!frmActiveAnswer!cboAnswerID

On Error Resume Next
ParentDocName = Me.Parent.Name

If Err <> 0 Then
GoTo Form_Current_Exit
Else
On Error GoTo Form_Current_Err

Rem ctlActiveAnswer.Requery
Rem Me.Parent![frmActiveAnswer].Requery
End If

Form_Current_Exit:
Exit Sub

Form_Current_Err:
MsgBox Error$
Resume Form_Current_Exit

End Sub


Private Sub txtQuestionID_BeforeUpdate(Cancel As Integer)

Rem Set ctlActiveAnswers = Forms!frmActiveAnswer!cboAnswerID

End Sub
Private Sub cmdNextQuestion_Click()
On Error GoTo Err_cmdNextQuestion_Click

DoCmd.GoToRecord , , acNext
' DoCmd.GoToRecord acDataForm, Parent!subActiveAnswer, acNewRec

Exit_cmdNextQuestion_Click:
Exit Sub

Err_cmdNextQuestion_Click:
MsgBox Err.Description
Resume Exit_cmdNextQuestion_Click

End Sub

 

[dan-cat]

The easiest way to requery a combo-box is to make sure you save the record and use the re-query command at its 'On Enter' event. For instance in your case:


Private Sub cboAnswerID_Enter()

DoCmd.RunCommand acCmdSaveRecord
Me.cboAnswerId.Requery
End Sub

I'm not totally sure what you are after with your sub-forms. Do you mean you want to sync the two together. I think I'll probably need to look at your db to see what you mean.

By the way my yahoo address was dan_cat1793@yahoo.com

Did your other db work out ok?

 

[Kila]

Hmmm. I will look at it when I get home. It is 5pm here and I am leaving now. I will pick this up at home.

Yes, I mean to sync them together. Rather than go thru all the details again, here is the link:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=60559

Active Answers has the answers, ActiveQuestions has the questions. They need to advance together, updating the combobox in ActiveAnswers each time so that the question displays the appropriate answers. BTW...in case the e-mail ever shows up, I will PM the instructions on getting into it.

Yes. The other database was just great. Thank You! I did send you an e-mail (that did not go thru) with the following minor change:


OK. This is great! Now I am trying to get it to hold the SSN that was typed in if the search does not find it in the SSN field...i.e. a new entry. (I am trying to minimize retyping). I am trying something like this, but it is not working. I am still tweaking in the meantime, trying to figure this thing out. You must excuse me. I am a bit new to VB. I've got books, but I still don't have the hang of the syntax.

Private Sub cmbSSN_AfterUpdate()
If IsNull(Me.cmbSSN) Then
Me.Filter = ""
End If

DoCmd.ApplyFilter "[SSN] = Forms!frmCustomersPOP!cmbSSN"

On Error GoTo Err_cmbSSN_AfterUpdate

Err_cmbSSN_AfterUpdate:
Set SSN = cmbSSN
Resume cmbSSN_AfterUpdate

End Sub

 

[Kila]

That was WORK!

It was WORK to shrink that thing down & move all the unneccessary stuff & references to it. At any rate, the best I could do was to get it down to almost 200,000 bytes...still too big! I did find a split-zip feature on my WinZip program. So I will send the 3 parts. Here is what WinZip said about reading split zip files:

The file format used by WinZip® to create split Zip files is an extension to the Zip 2.0 standard. As a result, some Zip utility programs may not be able to open split Zip files.
However, the only differences between split Zip files and spanned Zip files, which are supported by most Zip products, are the names and the locations of the parts of the file:

· Each part of a split Zip file has a different extension (name.Z01, name.Z02, etc., and name.zip for the last part). All parts of a spanned Zip file have the same name (name
.zip).
· The parts of a split Zip file can all be located in the same folder; the parts of a spanned Zip file must reside on separate diskettes (because they all have the same name).

Internally, the parts of split Zip files and spanned Zip files are identical. Therefore, you can convert from one format to another if necessary by simply copying and renaming the parts as follows:

To convert a split Zip file to a spanned Zip file

1. Copy each part of the split Zip file to a separate diskette.
2. After copying each part, change the filename extension to .zip. For example, January.Z01 becomes January.zip, January.Z02 becomes January.zip, etc. Only the last part of the split Zip file does not have to be renamed, because it already has a .zip extension. (I have already done this...it would not let me attach the Z01 extension.)


Here goes part 1...

 

[Kila]

Part 2...

Here is part two. You will have to be sure to keep them in order, as they all have the same name.

 

[Kila]

And Part 3!

Here is Part 3!

 

[Kila]

Sorry!

Sorry about the extra hassle...the database is complex, with four interrelated forms that reference 11 tables via 5 queries that make the world go round. I was told that this was the best way to create a survey database that would be easy to add additional questions/answers/surveys to later or respond to committees who swear they will never change the survey, and then change it. It's based on ID numbers rather than the text, but that requires go-between tables for everything, and queries to pull the related text back out.

 

[dan-cat]

I've just got your email come in to my openworld account.
It never rains but it pours. Am taking a look now for you.

Wish me luck!