I have a form with 80 - 200 rows of questions.
The Questions has a field for username/agent, that the supervisor manually has to enter because of the way the db i created.
How can I make a makro/function or something to automate this.
Se the attached form for further explanation.
I was thinking of adding an unbound field at the top of the form, and a button. When the button is pressed the form automatically filles in the value in alle the "AGENT" fields.
The form shows the AGENT control as a combo box. Generally, the purpose of a combo box is not automation. But, regardless, from the information you have provided thus far, it is impossible to figure out if this can even be done. First of all, in order for it to BE possible (with a purpose, that is), AGENT has to be related to the other information on the lines (somewhere in the tables) BEFORE you write the automation code, unless of course you are just wanting to fill AGENT with arbitrary values that have no meaning or relation to anything else....
I know you can make queries that does this, but I havent managed. I thought it would be easier to di this directly in the form with Vb (now I'm not so sure anymore).
I have trieed to explain the structur below:
I have a query as the datasource for the form, and its only one active agent from this combobox. This makes it possible for the supervisor to tab himself through the questions hitting the first letter of the agents initials. This box can be changed to a textbox with ease, and is only made this way for "speed".
Now things get complicated. The agents take this test on the local intranett, and does not have MSaccess installed on their citrix clients. I therefore use DAP's. With Daps you can not register data if the dap contains data from more than one table (i dont know how to write stored procedures).
The solution was to make ready all the questions by giving a default answer, and giving only one agent access to the quiz, the "active" agent.
The relationship between the tables are as following (it norwegian):
tblTest - tblTestDel - tblSPM - tblSvar - tblAgent
translated to english:
tblQuiz - tblQuizPart - tblQuestions - tblAnswers - tblAgent
They are directly related, all primarekeys are the same as the tablename including an "ID" at the end, all primaries are autonumber, exeption in tblAgent were the primary is text size 3 (the agents initials).
Now, is this enough information on how to autmate the "fill in the agents" question?
So DAP's is a substitute for MS Access?? I know with Access, if someone is working in a database that has an account in an assigned workgroup, you can display their name with "CurrentUser". Say...
I not that experienced with access, but I think you make the problem larger than it is.
Problem:
I have a value in the form, example XXX, and want a button so that XXX is filled in ALL rows below when I click it.
The form has 3 table sources, tblQuestion, tblAnswer and tblUser.
XXX is going to the table tblAnswer.
Is this possible?
The form in the attachment is in Access, and the supersvisor use this form to "prepare" each quiz.
The supervisor has to prep 60 quizzes, each with 80 questions, and I figure that it has to be a better way of doing this, without having to change the entire structure of the db.
The Data Access pages are html pages that the Agents use to answer the different quizzes, nevermind if they are active or not.
A "normal" quiz db would have something like a tblsession were you enter username one time, then get a sessionID that follows you all the way until the quiz is finished. Because of how the DAP and DB are created you cannot operate with "subforms". It should of course have been written with Sql and Asp, but its not, and we have to live with that.
Taken this under consideration, we end up with the same problem.
Please see the attachment, I have tried to explain it further there, and I think it will explain simple problem.
Thanks ajeTrumpet... it almost worked out perfectly.
It inserts the value into the first row, but doesnt "finish" the whole 80 questions.
Can the code be modified so it will finish off the whole 80 questions?
Code:
Private Sub Kommando11_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
Dim c As Control
For Each c In Me.Controls
If TypeOf c Is ComboBox Then
c = Me.inputINI
End If
Next
End Sub
Are you getting an error message, or does it just enter the value in the first line's Box??
The "For, Next" block loops through all controls to check the type of control. If the rest of the combos are not populating, I would suspect they have different properties somewhere. Any other code associated with the form besides this???
I dont get any errors, it just fills the value in the first box and stops there.
The other rows are left untouched.
I have som more code in the form for logs etc. They shouldnt be a problem, but what do I know...(?)
The code I got from you is at the bottom.
Code:
Private Sub Form_Close()
Log Me.Name, "Closing form"
End Sub
Private Sub Form_Open(Cancel As Integer)
Log Me.Name, "Opening form"
End Sub
Public Sub Log(strFormname As String, strRemark As String)
Dim strSql As String
Dim strUsername As String
Dim strLogname As String
strUsername = NetworkUserName()
'strLogname = apiGetUserName()
strSql = "INSERT INTO tblFormLog ( Username, Logname, Formname, [Timestamp], Remark ) VALUES (" & _
"'" & strUsername & "', '" & strLogname & "', '" & strFormname & "', '#" & Now() & "#', '" & Left$(strRemark, 255) & "')"
CurrentDb.Execute strSql
Exit_Log:
Exit Sub
Err_Log:
Resume Exit_Log
End Sub
Private Sub Kommando11_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
Dim c As Control
For Each c In Me.Controls
If TypeOf c Is ComboBox Then
c = Me.inputINI
End If
Next
End Sub
SELECT VL.VLini, VL.TESTaktivert, VL.Aktiv, tblSYS.DBstatus
FROM VL, tblSYS
WHERE (((VL.TESTaktivert)=Yes) AND ((VL.Aktiv)=Yes) AND ((tblSYS.DBstatus)=Yes));
Yes, there is a datasource for the form. I have used a leftjoin relation in a query between the table tblSPM (questions) and tblSvar (answers).
TblSvar has the relation to the table VL which includes all the users. (pk is VLini, text 3)
Some translation is required here:
Spørsmål/SPM = Questions
Svar=Answers
Aktiv=active (just a YES value)
The source for the form is the query QryQuiz_klargjør:
Code:
QryQuiz_AktivSPM.Spørsmål, QryQuiz_aktivAgentSvar.Svar, QryQuiz_aktivAgentSvar.VLini
FROM QryQuiz_AktivSPM LEFT JOIN QryQuiz_aktivAgentSvar ON QryQuiz_AktivSPM.SpmID = QryQuiz_aktivAgentSvar.SpmID;
The source for QryQuiz_aktivSPM is:
Code:
SELECT tblSpm.SpmID, tblTest.testID, tblTest.Aktiv, tblSpm.Spørsmål
FROM tblTest INNER JOIN (tblTestDel INNER JOIN tblSpm ON tblTestDel.TestDelID=tblSpm.TestDel) ON tblTest.testID=tblTestDel.TestID
WHERE (((tblTest.Aktiv)=Yes));
and the source for QryQuiz_aktivAgentSvar is:
Code:
SELECT tblSvar.*, VL.Aktiv, VL.TESTaktivert
FROM VL INNER JOIN tblSvar ON VL.VLini = tblSvar.VLini
WHERE (((VL.Aktiv)=Yes) AND ((VL.TESTaktivert)=Yes));
The whole DB is a hopeless web of queries.
I understand if you dont want to spend more time on this issue, I really appreciate your effort, and if you could assist me some more that would be great!
SELECT VL.VLini, VL.TESTaktivert, VL.Aktiv, tblSYS.DBstatus
FROM VL, tblSYS
WHERE (((VL.TESTaktivert)=Yes) AND ((VL.Aktiv)=Yes) AND ((tblSYS.DBstatus)=Yes));
doesn't make much sense. Something like that should usually be found in a ROWSOURCE. I am thinking that you wrote that because you are trying to tell me where the values of the combo box come from. That's OK though. And as far as the other codes you have written in your last post, that is exactly what I was looking for!!!
I think what happened here is my misinterpretation of your screenshot. See, I use Access 2007 most of the time, and when you create an AUTOFORM using the built-in functions of the program with this version, Access automatically aligns the field controls of the table VERTICALLY on the form (no matter how small the field character size is). In Access 2003 or earlier, it does not do this. If you create and AUTOFORM in an earlier version, the field controls appear aligned HORIZONTAL of each other on the form, until the screen space is used up. That's exactly what you're form looked like to me. But, if I'm right about this, your screenshot shows a CONTINUOUS FORM!!
So, I'm trying to make a connection here....If the RecordSource for your form....
Code:
QryQuiz_AktivSPM.Spørsmål, QryQuiz_aktivAgentSvar.Svar, QryQuiz_aktivAgentSvar.VLini
FROM QryQuiz_AktivSPM LEFT JOIN
QryQuiz_aktivAgentSvar ON QryQuiz_AktivSPM.SpmID = QryQuiz_aktivAgentSvar.SpmID;
takes three fields total, and you have three column of LIKE controls on your continuous form with the combo box associated with this....
Code:
SELECT VL.VLini, VL.TESTaktivert, VL.Aktiv, tblSYS.DBstatus
FROM VL, tblSYS
WHERE (((VL.TESTaktivert)=Yes) AND ((VL.Aktiv)=Yes) AND ((tblSYS.DBstatus)=Yes));
then I assume V.VLini refers to the AGENT column of the appropriate table??
The code I gave you originally looped through all the controls on a form to populate all 80 combo boxes, but if we're really dealing with a continuous form here, there is only ONE combo box on the form!! Thus, that would explain exactly why you were only getting the top box to be populated; because that was the current record at RunTime, and also it is the only control on the form, along with 79 copies of it, each representing its own record.
So, how about we loop through the underlying table's field RECORDS instead of the ONE control??? Replace THIS...
Code:
Private Sub Kommando11_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
Dim c As Control
For Each c In Me.Controls
If TypeOf c Is ComboBox Then
c = Me.inputINI
End If
Next
End Sub
With THIS....
Code:
Private Sub Kommando11_MouseUp ([color=red]Why use MOUSEUP?? I'd Choose Something else, for specificity's sakep/color])
Dim rs As Recordset
Set rs = Me.RecordsetClone
With rs
.MoveFirst
Do Until .EOF
.Edit
![ControlSourceOfComboBox ([color=red]MUST be a field name here!![/color])] = Me.inputINI
.Update
.MoveNext
Loop
End With
rs.Close
Set rs = Nothing
End Sub
Your assumption is correct, VLini is the agents initials, and also the value i want to appear in the box.
You are also correct about the form being continous.
I think I got the line under correct... (should return the correct VLini)
!QryQuiz_aktivAgentJA(VLini) = Me.inputINI
...and assume that I am supposed to transfer the fieldname to the first line
Code:
Private Sub Kommando11_Click(VLini)
I get an error, and thats probably because of the value I have placed in the first line. Error message: Procedure declaration does not match description of event or procedure having the same name
The Code:
Code:
Private Sub Kommando11_Click(VLini)
Dim rs As Recordset
Set rs = Me.RecordsetClone
With rs
.MoveFirst
Do Until .EOF
.Edit
![QryQuiz_aktivAgentJA(VLini)] = Me.inputINI
.Update
.MoveNext
Loop
End With
rs.Close
Set rs = Nothing
End Sub
Yes, I think so...after the "_", you are telling Access that there is a form event called Click(VLini). I've never heard of that... Take the agent name out of the line, because you are just specifying the event on which to perform the action. Also...after you replace the code, go back into the Form's property sheet and check to see there is still an "Event Procedure" listed under the "OnClick" Event. Make sure it's not deleted. I've seen it erased when doing this, but more commonly it happens when you change the control name in Visual Basic AFTER there has already been a procedure written for the event.
I have a commandbutton next to the unbound field, and when the mousebutton releases, the code runs to magically fill in alle the comboboxes in the continuous form.
A programmer just looked at the coding, db-structure and so on.. He was in a hury, but checked the debugger, and couldnt find any reason for why this code didnt work.
He made a few changes, to thee code, and ended up here:
Code:
Private Sub Kommando11_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
Dim rs As Recordset
Set rs = Me.RecordsetClone
With rs
.MoveFirst
Do Until .EOF
' .Edit
!VLini = Me.inputINI
.Update
.MoveNext
Loop
End With
rs.Close
Set rs = Nothing
End Sub
He said that every query/table in use seemed ok regarding the ability to update, and the code looked ok (he ment that the .edit command was of no use), and he couldn find any other reason for why it doesnt work.
I have a commandbutton next to the unbound field, and when the mousebutton releases, the code runs to magically fill in alle the comboboxes in the continuous form.
A programmer just looked at the coding, db-structure and so on.. He was in a hury, but checked the debugger, and couldnt find any reason for why this code didnt work.
He made a few changes, to thee code, and ended up here:
Code:
Private Sub Kommando11_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
Dim rs As Recordset
Set rs = Me.RecordsetClone
With rs
.MoveFirst
Do Until .EOF
' .Edit
!VLini = Me.inputINI
.Update
.MoveNext
Loop
End With
rs.Close
Set rs = Nothing
End Sub
He said that every query/table in use seemed ok regarding the ability to update, and the code looked ok (he ment that the .edit command was of no use), and he couldn find any other reason for why it doesnt work.
The debugger ends up here with a runtime error 13 - type mismatch
When I click a forms property, I go under the "data" tab, and the first line there indicates the recordset which is QryQuiz_Klargjør (a query). The form works great if I do the job manually, tab through it and punch the first letter in the initials...
Let us say that the function works, then I wouldnt need the comboboxes, it could be normal textboxes... would that make any difference on the coding? I tried to change it to a textbox, and change the source to the vlini in the forms datqasource, but ended up with the same message from the debugger. Would a textbox require any different coding than the combobox?
You (and the other guy), say that It seems that it could be something with the recordset... Could you explain to me what may have gone wrong there, and maybe that is where the problem is and not the coding.
You (and the other guy), say that It seems that it could be something with the recordset... Could you explain to me what may have gone wrong there, and maybe that is where the problem is and not the coding.
Well, the one thing that sticks out in my mind is when you posted the Control Source of the combo box...that should not be right, the ControlSource for you should be a FIELD NAME in the underlying query, not an SQL statement, which is what you have right now. This is obviously not correct, unless you have mistakenly looked at the ROWSOURCE property of the form and assumed that it said "ControlSource".
The type mismatch error does indicate a problem with the underlying recordset. When you write LOOPS and such with Visual Basic to change field values, you have to do it in the recordset from which the form is based, because if you do it in the form object, it will only loop through the CURRENT PAGE, which is basically the current record that is being displayed from the table.
A CLONE is just a property of a recordset, but it needs to be opened before you can perform an action on it. Think of it as a temporary "copy" of your form in datasheet view. Any updates to the CLONE will be reflected in your recordset (and the related form!), just like if you updated a query that pulled records from a table. You can update the query, and those updates will automatically be relected in the underlying table (provided the query IS updatable to begin with - e.g. - is has no function / calculations in it)
autopopulate.gif
