Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    New Lounger
    Join Date
    Jul 2003
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Check boxes (Access 97)

    Does anybody know how to make a check box work in Access 97? Im a newbie when it comes to access!!! I can add the check box in the design view but when i open the form and try to check it, it wont tick!!! Once the box is ticked I want the info on the form to transfer into a new table. Can anybody please help me!!!!
    Cheers,
    Rob.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Check boxes (Access 97)

    Check boxes are usually linked to a binary Yes/No field. There aren't often use to cause data to be saved or similar actions. When you use bound forms, data is automatically saved when you move to another record or close the form. Does this help?
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check boxes (Access 97)

    Is your form bound to a table (or query)? In other words, does the Record Source property of the form contain something?

    If so, you can set the Control Source property of a check box to the name of a field of type Yes/No. The check box will automatically show the value of the Yes/No field (ticked = Yes / True, clear = No / False), and if the user ticks or clears the check box, this change will automatically be stored in the table when the user moves to another record or closes the form.

    If your form is unbound (the Record Source property is empty), transfering data from the form to a table would involve writing VBA code. Although this is not extremely complicated, I wouldn't recommend it if you are a novice in Access. Bound forms are much easier.

  4. #4
    New Lounger
    Join Date
    Jul 2003
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check boxes (Access 97)

    Hey Hans thanks for the reply.
    Yes there are lots of different records bound to the form. There are about 15 records the form gets from the tables. I tried changing the control source to the yes/no like you said and the check box doesnt work i get a message saying "cannot be edited its bound to unknown field "yes/no" every time i try to check it!! Originally I had the control source named as "Apples" and in the table A that the info from the form goes into i had a field named "Apples" and i kept getting a beeping noise and a message saying "cannot be edited its bound to unknown field "apples" every time i try to check it!!!
    Does this sound like the from is unbound??
    Thanks for the reply in advance

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check boxes (Access 97)

    You certainly can't put Yes/No in the control source of a check box; it is supposed to hold the name of a field, not a data type.

    Open the form in design view.
    Activate the Properties window.
    Make sure that the caption of the properties window says "Form".
    If it doesn't, select "Form" from the Object dropdown list in the Formatting (Form/Report) toolbar.
    Activate the Data tab of the Properties window.
    Look at the first property, labeled "Record Source".
    This property should contain one of three things: either
    (1) The name of the table that contains the records to be displayed in the form,
    or
    (2) The name of a query based on the table that contains the records to be displayed,
    or
    (3) An SQL string starting with SELECT.
    If the "Record Source" property is empty now, your form is unbound.
    If so, click the arrow of the dropdown list in the "Record Source" box and select the name of the appropriate table.
    Then, click on the check box.
    The first property in the Data tab of the Properties window is now labeled "Control Source" instead of "Record Source".
    Click the arrow of the dropdown list, and select the name of a field. This field must have been defined as a Yes/No field when you designed the table.

  6. #6
    New Lounger
    Join Date
    Jul 2003
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check boxes (Access 97)

    Hans,

    Thanks for the help, I discovered my form was unbound as the record source property was empty and form is selected in the object format toolbar. However there is not one table that the info is taken from there are many tables with all the same fields for example Table1 has fields A B and C with info under A B and C, Table 2 also has fields A B and C but with different info and so on......

    The user selects a table of his choice from a drop down bar in form A eg Table 2. The info from table 2 is then displayed in form B.

    I made a field "Delta" in the table which supplies the headings A B and C to form B this table has no info in it. I would be inpractical to add a field "Delta" to all the tables table 3 table 4.......and so on! However I still get a beeping sound once i try to check the box and get a message saying "Control cant be edited it's bound to unknown field Delta". If the user wants to create a new table into the database then he would have to go off and create a field "Delta" in the new table that has been created!!!!!(The new table is created by clicking a button on another form) Is there a way when the new table is created that it automatically adds the field "Delta" into it the new table thereby enabling the check box??? and once the check box works how can i transfer the info in the table that the form displays into a new table by checking the box??
    I really appreciate the help you have given me,
    Cheers,
    Rob.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check boxes (Access 97)

    Hi Rob,

    I don't understand why you would want to use a check box to transfer data to a new table; to initiate actions like that, one usually employs a command button. You wouldn't have the problems you have with the check box now if you used a command button.

    Let's take a step back. If I understand your description correctly, you have several tables in the database with the same structure, and the user can click a button to create a new table.

    Of course, I don't know what your database is used for, but in general, this does not seem a very good idea to me.
    <UL><LI>Instead of creating several tables with the same structure, consider using one table with an extra field that contains an identifier; for records originally from Table1 this identifier could be 1, for records originally from Table37 this could be 37, etc. You can then use the one table as Record Source of a form, and filter it to display only records with a specific value of the identifier field.
    <LI>Giving the user the ability to create new tables is a recipe for performance and maintenance problems. Using one table as mentioned above is less likely to pose problems.[/list]Again, I don't know what your database is used for, and my remarks may be completely off target, but perhaps you can use them.

  8. #8
    New Lounger
    Join Date
    Jul 2003
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check boxes (Access 97)

    Hi Hans,
    Thanks for the suggestion. I could do it that way alright just a few questions
    1. Ok I can I use a command button to add the info in the form to the new table, how do I achieve that? I have never dealt with buttons before or transfering data into new tables!!!
    2. Say if somebody clicks the command button and then realises they made a mistake would there be an easy way to rectify that?
    Cheers for the help,
    Rob

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check boxes (Access 97)

    You would have to write Visual Basic code to transfer data from the form to a table. Although this is not extremely complicated, it requires some familiarity with VBA programming and with the data model underlying Access.

    I really think that you could simplify your database considerably by storing your data in a single table. You probably could use a bound form then, so that no code is needed to transfer data from the form to a table; this would be taken care of automatically.

  10. #10
    New Lounger
    Join Date
    Jul 2003
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check boxes (Access 97)

    Do i need to write a visual basic code to transfer one table to another table?. So when i click the command button the info on the form is being read off a table with all the same fields that is needed to go into the new table.

    If I was to just have one table how would I get the button that creates a new form to add it to the single form? The user adds in info into form X one of those fields is called ref number say he enters 964, he then clicks ok once he does that a new table is created with the ref number as the title of it eg 964. He then goes to form A and locates the new table created in a drop down bar. He selects that and clicks a button that brings him into Form C. Form C contains info about the new table called 964 he just created, there could be up to 1000 different rows of info with the same fields. He cycles through them and clicks the command button so the info displayed which is being read off the table called 964 is sent to a new table. Would it be possible to have the command button to take the info from the row in the table that is being displayed in the form and transfer it into a new table. So its a table to table transfer!!!
    Cheers,
    Rob.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check boxes (Access 97)

    I don't know if we are using the terms "form" and "table" in the same sense. In an Access database, these terms have a specific meaning.

    A table is a basic unit to store data. A table is organized into rows (records) and columns (fields). A column (field) contains a specific item of information, for example a birth date, or a street address. A row (record) contains the various bits of information that belong together (a person, company, order, ...)
    You can view tables in the Tables tab of the database window.

    A form is an object used to view, enter and edit data; a form contains controls such as text boxes, combo boxes etc. You can view forms in the Forms tab of the database window.

    You description of new forms and new tables being created confuses me. The database developer creates new tables and new forms when he/she is designing a new database. When the design is ready, the database will be used to add, modify and remove data in tables, but in general, the user doesn't add new tables or ne forms. Why do you want to transfer data to a new table? I'm afraid I don't understand what you want to do here, and why.

  12. #12
    New Lounger
    Join Date
    Jul 2003
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check boxes (Access 97)

    You were saying it would be quite a simple code id write to get the info from the form to transfer into another table. I know how to get into the code area of the command button.
    Say there are 3 fields on form C that i want to transfer into the new table say the new table is called NEWTAB and the fields are called A B and C could you give me an idea of this code so i could apply it to my example.
    Thanks a million,
    Rob

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check boxes (Access 97)

    OK, here you go:

    This is code for a command button named cmdTransfer. It transfers all records from a table whose name is in the variable strSource to a table whose name is in the variable strTarget. You must fill these variables, since I don't understand what you are doing.

    Private Sub cmdTransfer()
    Dim strSource As String
    Dim strTarget As String
    Dim strSQL As String

    strSource = ... ' you must set the name of the source table here
    strTarget = ... ' you must set the name of the target table here

    strSQL = "INSERT INTO [" & strTarget & "] (A, B, C) SELECT A, B, C FROM [" & tblSource & "]"
    DoCmd.RunSQL strSQL
    End Sub

    The user will be prompted to confirm the transfer.

  14. #14
    New Lounger
    Join Date
    Jul 2003
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check boxes (Access 97)

    Hi Hans,
    I was trying to work your code but have encountered a problem. There is not one source table. The source tables are created by the user and are named by a ref number eg 2343 Is there a way you can get the code to recognise the ref no as the source table by telling it to take the ref number (2343) from the form in field "REF" or the title of the table eg 2343. The ref nos are random. The target table NEWTAB remains the same and doesnt change.
    Thanks a mil for the help,
    Rob.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check boxes (Access 97)

    Hi Rob,

    If I understand you correctly, you should be able to set strSource in the code in my previous reply:

    strSource = Me.REF
    strTarget = "NEWTAB"

    This will set strSource to a value that depends on the contents of REF on the form, and strTarget to the fixed name "NEWTAB".

    Note: in the code I posted yesterday, I forgot one ampersand ( & ), I corrected this today.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •