Full Version: User input add records - choose values for field
UtterAccess Forums > Microsoft® Access > Access Forms
DaisySara
Hello,
I am creating a form to accept user input to add records to 2 tables, with a 1-to-many relationship. In a nutshell, table A accepts ID number, address, etc. and Table B will assign different disciplines to this ID, using the field "disciplines". There are only 5 choices for this field, so I would like to have check boxes on the form, letting the user choose 1 or many of these disciplines, which will then add 1 record for each discipline into Table B. I am stumped as to how to do this.
xample
Table A
ID = 10
address = 13 Elm Street
Table B
ID = 1
TableA ID = 10
Discipline = EL
ID = 2
TableA ID = 10
Discipline = ST
Thanks.
fkegley
I would do this with 3 tables---TableA as it already is, TableC would contain one record for each discipline, a disciplineID and disciplineDescription. A third table, TableB would contain one row for each different TableA ID-TableC ID combination.
would develop a form that was bound to TableA that had a subform control on it that was bound to TableB. TableC would serve as the source of a combo box to allow intelligent entry of the DisciplineIDs into TableB.
Jack Cowley
You will have to write code to do what you want or you could have a combo box as the Disipline control in the subform and let the users select as few or as many disciplines as they like from the list in the combo box. If you go the code route you will need 5 unbound check boxes on your form and then a command button to append the data depending on the check boxes checked. You can create 5 Append queries (that might be the easiest if you are not familiar with code) and 5 If - Then statements to run the queries. This is some 'air' code:
oCmd.SetWarnings False
If Me.Checkbox1 Then
DoCmd.OpenQuery "Query1"
End if
If Me.Checkbox2 Then
DoCmd.OpenQuery "Query2"
End If
...etc...
DoCmd.SetWarnings True
hth,
Jack
DaisySara
Thank you I will try that. Should the "if" statements be on the form close event?
Jack Cowley
I would add the 5 unbound check boxes to your from and I would add a command button. I would put the code behind the command button so the user can make their selections and then when they have the ones they want they can click the command button. If you want the data to show, after the user clicks the button, you will need to requery the subform (if it is part of your current form) so that the new records show.
Good luck!
Jack
DaisySara
Sorry nevermind, I see you said to use a command button...I will try it.
DaisySara
Actually I am concerned the user will not always use this button and either just close the form, or hit next to add another record (more likely).
Jack Cowley
You can set a flag when the user clicks the command button and then check the flag in the Forms Before Update event. If the flag is not set then send the user back to the form to click the button. You could also check the flag in the forms On Unload event if you prefer....
th,
Jack
DaisySara
I am testing out some code...I have gotten it to work in the form close event, but for some reason the same exact code does not work behind a command button...I can't figure out why. I inserted the message box just as a test, that displays fine but the record in the disciplines table is not being inserted.
Private Sub cmdAddRecord_Click()
Dim strSQL As String
If (Me![cmbItemType] = "Occupational License") Then
strSQL = "Test Occ Lic"
MsgBox strSQL
strSQL = "INSERT INTO Submittal_Disciplines(Submittal_ID, Discipline)"
strSQL = strSQL & " VALUES(" & numRecNum & ", 'CO Clerk');"

CurrentDb.Execute strSQL


End If
End Sub
Jack Cowley
Hmmm. Your test strSQL will not tell you much as it takes place before the 'real' code. If this works in the On Close event of your form it should work in the On Click event as well. I have tried the code below and it worked for me:
CODE
Dim strSQL As String
If Me![cmbItemType] = "Occupational License" Then
strSQL = "INSERT INTO Submittal_Disciplines(Submittal_ID, Discipline)"
strSQL = strSQL & " VALUES(" & numRecNum & ", 'CO Clerk');"
CurrentDb.Execute strSQL
End If

Try it again and if it fails then put a Stop in before the If line of code and when you click your command button the code editor will open. You can step through your code using the Step-Into icon and see if you can see any anomolies as the code executes. As I mentioned it is working for me in a command button so it should work for you... If it still fails create a demo or attach your db so we can take a look at it and see if we can figure out what is going on. NO guarantees that we can figure it out, but we will give it a shot.
Jack
DaisySara
Thank you so much, but tried it and it still doesn't work...I am stumped. Here is my database. When the switcheboard opens it is the first form I am working on, Enter Submittals. This form sends records to the Submittals_Main and Submittal_Discipline tables.
Jack Cowley
DaisySara -
I'm sorry to say that your attachment is not 'attached'. Be sure and compact your db, then zip it and attach. The maximum size for your attachment is 500k. I will be here for about 40 more minutes (it is 7:20am) and then I will be gone for a few hours. Just wanted you to know that I am not ignoring you....
Jack
DaisySara
Sorry about that...the file was too big...should be there now.
Jack Cowley
DaisySara -
I have tried your form and clicking on the command button does add the SubmittalID and Discipline data to the table "Submittal_Disciplines". It also adds it when I close the form, as it should.
Unfortunately I have to leave right now, but I will be back in about 3 hours. Do you get any errors when you run your code?
I will have some other hints for you about naming conventions and other things you should consider when it comes to the normalization of your db when I return...
Jack
DaisySara
The command button works for you? With the code commented out of the form close event, and only using the command button it does not add the record into the disicplines table. I do not get any error. The record is entered into the main table, but the corresponding disciplines record is not being added.
Jack Cowley
DaisySara -
Yes, the command button works for me. I select "Occupational License" from the combo box and click the command button and the 'numRecnum' and 'CO Clerk' is added to the table. I remarked out the code in the On Close event as it was adding an extra record. Did you compile your code and not receive any errors? Have you done a compact and repair of the database? As a last resort create a new, blank db and import everything from your current db into the new one and cross your fingers....
I do not know what to tell you as the program is working for me. There are some things that you should do in future databases and they are to use standard naming conventions for you objects; do not use spaces in object names; all tables should have a primary key of an autonumber; Option Explicit should be added to your code modules directly beneath Option Compare Database....
At this point I do not know what else to suggest as the db and the code is working for me.
Jack
DaisySara
Very odd. It does not work for me, or others that have been trying to help me out. Thank you so much for all of your help, I appreciate it.
Jack Cowley
DaisySara -
I'm sorry that I don't have a solution for you, but since the code works for me and I have created another small db and it worked in that, I don't know what else to suggest. You may want to post your demo again, in a new thread, and someone else will look at it and maybe they have an answer as why this is not working for you.
My very last suggestion would be to create a simple new form with just a command button and an unbound text box and put your code in the button. Enter a value into the unbound control and click your button and see if it adds the data to the table. I did that and it worked for me, along with the demo db you sent.
Good luck!!!!
Jack
DaisySara
Regarding your post about creating a flag, if the command button is not clicked. I have never done this before and do not know the syntax...can you explain further??
Jack Cowley
DaisySara -
This will be fast as I am about to leave the UA building until my next shift at 2pm Pacific Time...
1. In a Standard Module add code like this just below Option Explicit at the top of the code page;

Public varMyVariable As Integer
2. In the On Click event of the command button:
varMyVariable = 1
3. Wherever you want to test if you have click the button:
If varMyVariable = 1 Then
The above lets you know that you have clicked the button. Reset the variable:
varMyVariable = 0
hth,
Jack
DaisySara
Yes that worked, thanks once again. Just one thing...I've included a vbokonly message box...How do I program the message box to go back to the form to allow the user to hit the button, rather than closing the form?
Jack Cowley
I'm not completely sure exactly what it is you want to do but if you have a vbOK only button then remove your DoCmd.Close code after your MsgBox code:
all MsgBox("Click OK to continue", vbExclamation, Application.Name)
DoCmd.Close <---Remove this line of code
Now the Msgbox will close the form will remain open. I am not sure of the purpose of your MsgBox so post the code if this is not what you want.
hth,
Jack
DaisySara
I'm not even sure how it should work. I don't want the user to be able to close the form or hit next without clicking the command button. I was thinking I could either have an OK Only message box telling them to click the button, and when hitting OK it takes them back to the form. Or a YesNoCancel button, asking if they want to add the record. Yes would run the code in the button, no would close all without saving and cancel would bring them back to the form.
So first of all I should put this message box in the form, before update event, correct? I am not very good with message boxes and the syntax. I had this very simple code for a VBOKOnly,
im Continue As String
Continue = "Please hit the button to add record"
msgbox Continue
But after hitting OK, this closed the form.
Jack Cowley
DaisySara -
et me see if I understand what you want to do - You do not want the user to be able to close your form or move to a new record until the form is complete or a particular control as specific data. Am I correct so far? If I am correct then here are a couple of fundiments:
In the forms Property Sheet set The Close Button property to No. The user can click the X button in the upper right corner to close the form.
Set the Navigation Buttons property to No so the user can only move to a new record by clicking a button that you create using the Wizard.
To close the form add a button to close the form. If you need to validate the data entry into the form then do that in the Forms Before Update event.
Does this help? Can you post your db so we can see what you are trying to do (compact and zip, 500k max size)?
Jack
DaisySara
Yes that does help...I didn't know you could turn off the navigation buttons. Here is my database...it is the same form I have been working on, the Enter Submittals. The one button contains the code to add records, and I want to make sure this is hit...
Jack Cowley
DaisySara -
I have your db and I am going to make some changes to the form in question then I will send the program back to you and we can have a place to work from...
Jack
Jack Cowley
DaisySara -
I have added the following buttons to your form: Previous Record, Next Record, Add New Record and Close Form. What you would like to happen behind the Close Form and Add New Record buttons? I think that the code should be pretty much what you have now behind the Add New Record button, but if there is anything that needs to be checked (one of the checkboxes maybe) then let me know and I can add that code.
Jack
PS. I removed the navigation buttons too.
DaisySara
Wow, thank you so much...Behind the close form I want the form to close and go back to the switchboard. Behind add new records I want it to add the record (with the code we created for the checkboxes) and move to a blank record, ready for a new record...
Is it attached?
Jack Cowley
DaisySara -
I have not attached anything as I want to do the code now that I know what you want. I will be sending the db back to you, with the changes, within the hour...hopefully!
Jack
Jack Cowley
DaisySara -
Here is the revised db. It is not 100% complete but it should be pretty close. When the form opens it will open at the first record, not a new record, as it was before. This can be changed, if you like, back to the way it was. I also made a new table "Submittal_Disciplines" so I could be sure the code was working. There is no data in this table Your original table is saved as "Submittal_DisciplinesORIGINAL".
Give this a try and do not be surpised if there are errors as I have not tested it very much.
Jack
DaisySara
I can't believe you did that for me, thank you very much. I am going to go over your code and try to learn and understand what you did. I appreciate all of your help...I don't know what I would do without this forum(probably be out of a job!).
Thanks again.
Jack Cowley
DaisySara -
You are very welcome!! I am glad I have been able to help...
There is a fair amount of code floating around in the demo I sent you. Some of it has to do with closing or not closing the form depending on what the user selects in the message box that asks them if they want to save the record or not. There was also some error handling as the setup I used causes some errors. These are harmles errors so should not present any problem by being trapped. I think the only thing I may be missing was to undo some of the check boxes if particular method of leaving the form was choosen, but I can't remember at the moment.
The code is not terrible sophisticated, but I think it will do what you need.
If you have questions or problems just let us know. If you have questions about the demo then let me know....
Jack
DaisySara
Yes that code is working well. However, if I make any changes(not to the code itself) it does not add the records to the discipline table. For instance, I set up the relationship between the 2 tables, and it stopped working. Then I tried to go back to the original disciplines table(you had made a new one to work/test with), and as soon as I do it stops adding the records to the disciplines table.
Could this have something to do with the "me.dirty = false" line of code somebody had suggested before I insert?
It's just weird how my smallest change causes it not to work anymore.
Jack Cowley
DaisySara -
If you have a relationship between two tables and the Discipline table is the Many side of a One-to-Many relationship you may not be able to add records to that table if a corresponding record does not exist in the One side of the relationship. It is my guess that that is what is happening. Do you get and error saying something about needing a record in the other table?
Yes, a small change can make a huge difference, as you are finding out....
Jack
DaisySara
No error. It adds the record to the main table, just not to the many table (disciplines). When this was happening before I added the 'me.dirty=false' and it started working. I've tried adding it, but get an error, I am probably adding it in the wrong place.
Jack Cowley
DaisySara -
That is a possibility. Try putting the code in other places and hopefully that will fix the problem. Good luck with this!
Jack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.