Debugging SQL in VBA
Thanks for the info on debugging SQL in VBA.
Dave44000
Last Activity: Mar 12 '14, 03:07 PM
Joined: Oct 15 '13
Location:
-
Insert query quit working
I have a query that worked months ago, but after adding additional fields to the target table, the query no longer runs. It took values from text controls on a form. It no longer finds them, so it displays parameter messages instead.
I shortened it up just to test it and even with only one field it doesn't work.
The Patient ID, aka the Me.txt62 field is never null. I have tried setting the focus, but that didn't help.... -
So here is my SQL
Skip using the wizard. Write the usual query then convert it to a crosstab query select the row field and column...Code:TRANSFORM First(PatientTelephone.TelephoneNumber) AS FirstOfTelephoneNumber SELECT PatientTelephone.PatientID FROM PatientTelephone GROUP BY PatientTelephone.PatientID ORDER BY PatientTelephone.PatientID PIVOT PatientTelephone.PatientTelephoneType;
Last edited by zmbd; Dec 5 '13, 11:28 PM. Reason: [z{no edit{THANK YOU for posting this back and the supporting comment!!! BOL!}]Leave a comment:
-
Thanks for the crosstab mention and reference. I had tried one, but didn't do a good job on it. I looked at the article you referenced. Then I Googled it and found, http://www.youtube.com/watch?v=k_uLJK3mT2o, which did exactly what I needed.
It couldn't have been as hard as what I was doing or what I had tried earlier. And, yes, no vba. Great!Leave a comment:
-
I think you understand what I need given your last two examples. A crosstab query would still need some vba. I'll look at it again. Thanks.Leave a comment:
-
Lookup table IDs as Columns
I'm trying to mail merge a single record in MS Word. That works. The problem is that single record or row in Access can't be created via SQL alone. I can see how to use vba to get from where I'm at to where I'm going, but I don't want to use vba.
It seems to me that since combo boxes are so normal, writing this query should be normal as well.
...Code:PATIENT PATIENT TELEPHONE TELEPHONE TYPE PatientID PatientTelephoneID
-
Swapping lines of code changes results and error.
I have a form called Main, and a second form called Create.... I open the Create form from a combobox. Then, just click SAVE, which creates a record, and tries to set a text box to the ID for the new record and set the name of the entity of that new record to another text box.
I have many of these. They all work, except one. It is slightly different from the rest. Those two lines of assignments have worked while I was debugging. The... -
Thanks for telling me to rethink my solution.
It took me two more days of struggle before I came up with an easier way. Implementing that, I found an even easier way, far easier.Leave a comment:
-
Thanks!
The move focus tip broke the logjam. Moving the focus change to the last line in the sub made a difference. I almost ignored the behavior change after that, but I went back and put it in again.
I've gone with the on Enter event. I'll put the value in the cbo, the user will click the cbo, and all is well. Help text will suffice. The form works as needed.Leave a comment:
-
I added SetFocus to the NotInList event. It cleaned up some left over behavior in cboPOShort. It also selects the value in cboPhysicianOff ice. But, the relevant events are still not launching.
Code:Private Sub cboPOShort_NotInList(NewData As String, Response As Integer) Me.cboPhysicianOffice = NewData Me.Txt202 = NewData Me.cboPOShort = Null Me.cboPhysicianOffice.SetFocus End Sub
Leave a comment:
-
Assigning code
The source for the entry assigned to the cbo is cboPOShort, it lists those PhysicianOffice s already assigned to Physician. The Physician has already been selected.
Row source for cboShort is
The query in the SELECT (POShortListQ)...Code:SELECT [POShortListQ].[PhysicianOfficeID], [POShortListQ].[PhysicianOfficeName] FROM POShortListQ ORDER BY [PhysicianOfficeName];
Leave a comment:
-
I read option 4, and yes, I'm not letting them add items to the list. They have to create a number of records to create the item. The form is a data entry form.
The hair splitting is caused by the difference between typing an entry in, which works, and assigning a value programmaticall y. The former opens the form. The latter does not, because I cannot get it to do so.Leave a comment:
-
row source is
This works perfectly before the third CBO got involved. If an entry wasn't in the list, it opened a form via the NotInList event. Once the value is assigned from the third CBO, and it is still not in the list, the NotInList event does not ...Code:SELECT [PhysicianOffice].[PhysicianOfficeID] , [PhysicianOffice].[PhysicianOfficeName] FROM PhysicianOffice ORDER BY [PhysicianOfficeName];
Last edited by zmbd; Oct 15 '13, 05:50 PM. Reason: [Z{Please use the [CODE/] button to format posted code/html/sql/formated text - Please read the FAQ}]Leave a comment:
-
The properties are
Limit To List Yes
Allow Value List Edits Yes
Enabled Yes
Locked No
Allow Values List Edits is Yes, because the form that gets called adds a record and a the list is refreshed.Leave a comment:
-
Dave44000 started a topic What combobox events are triggered when an assignment is made to it via vbain AccessWhat combobox events are triggered when an assignment is made to it via vba
I have a combobox (cboTwo) where you type in an office name, and if that office name is not in the list, the NotInList event opens a form. I have another combobox (cboOne) and create a record associating the entries in both comboboxes (many-to-many).
A user wants cboTwo to act like a normal combobox (one-to-many). The problem will be that existing records could legitimately end up being duplicated. Actually, not written, since I've...
No activity results to display
Leave a comment: