Results 1 to 9 of 9
  1. #1
    Dano60 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2015
    Posts
    32

    Making changed with a macro

    Hi all,



    Is it possible to run a macro that would open a table in design view and go change in a field the type of data? How is it done if possible?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Macro might be able to call an ALTER TABLE sql action. Why would you want to programmatically modify table design?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Dano60 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2015
    Posts
    32
    Hi VIP,

    I'm trying to modify and existing database that an employee did a while back and left the company.

    I have an original query that return pieces of various length of lumber. I did a table query from that query and the data in the field in the length of lumber is in text as data type.
    Now I'm running a append query to change the length of the lumber to the next available size because we do not carry the length anymore.

    IIF([Reliure_grosseur] In (8;10;11;12;14;17;18;19;20);[Reliure_grosseur];Switch([Reliure_grosseur] Entre 6 Et 7;8;[Reliure_grosseur]=9;10;[Reliure_grosseur]=13;14;[Reliure_grosseur] Entre 15 Et 16;17))

    Give me a message conversion error

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If the field type needs to be changed, would not need code to do that, just open table in design view and change data type. However, need more info. Open the table in design view and look at properties of the field. Is it number or text type? If it is text and you are saving number data (which it appears the case from your expression) then why is the data type not already number? If the field is text type then the data elements in your expression need apostrophe or quote delimiters.

    Is this field saving the ID of records from a lookup table? Is that ID field a text type?

    I would use comma instead of semi-colon but maybe is different in non-English database.

    Instead of Entre 6 Et 7 could just use <8.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Dano60 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2015
    Posts
    32
    Quote Originally Posted by June7 View Post
    If the field type needs to be changed, would not need code to do that, just open table in design view and change data type. However, need more info. Open the table in design view and look at properties of the field. Is it number or text type? If it is text and you are saving number data (which it appears the case from your expression) then why is the data type not already number? If the field is text type then the data elements in your expression need apostrophe or quote delimiters.

    Is this field saving the ID of records from a lookup table? Is that ID field a text type?

    I would use comma instead of semi-colon but maybe is different in non-English database.

    Instead of Entre 6 Et 7 could just use <8.
    Hey, Thanks for replying!

    I will not be the only one using the database, therefore the need for automation from a button. The data is taken from a query that take it from a table. I am not willing to change things from existing. As I am "afraid of fucking it all up" sorry for the language!!!!

    I have put the database as attachment so that you can look. The query that make the table is tablequery spirale and create the table Table spirale modifié.
    Attached Files Attached Files

  6. #6
    Dano60 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2015
    Posts
    32
    Where would I put apostrophe or quote delimiters so that the below statement would be reconnize in text?

    IIF([Reliure_grosseur] In (8;10;11;12;14;17;18;19;20);[Reliure_grosseur];Switch([Reliure_grosseur] Entre 6 Et 7;8;[Reliure_grosseur]=9;10;[Reliure_grosseur]=13;14;[Reliure_grosseur] Entre 15 Et 16;17))

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    None of the tables are local so there is no data to examine and test with. But try:

    IIf([Reliure_grosseur] In ('8';'10';'11';'12';'14';'17';'18';'19';'20');[Reliure_grosseur];Switch([Reliure_grosseur] Entre '6' Et '7';'8';[Reliure_grosseur]='9';'10';[Reliure_grosseur]='13';'14';[Reliure_grosseur] Entre '15' Et '16';'17'))

    Why are you programmatically creating and/or modifying tables?

    I see tables in Relationships window but there are no links so relationships are not established.

    I see [Event Procedure] in event properties but there is no VBA code. How did that happen?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Dano60 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2015
    Posts
    32

    Thumbs up

    Quote Originally Posted by June7 View Post
    None of the tables are local so there is no data to examine and test with. But try:

    IIf([Reliure_grosseur] In ('8';'10';'11';'12';'14';'17';'18';'19';'20');[Reliure_grosseur];Switch([Reliure_grosseur] Entre '6' Et '7';'8';[Reliure_grosseur]='9';'10';[Reliure_grosseur]='13';'14';[Reliure_grosseur] Entre '15' Et '16';'17'))

    Why are you programmatically creating and/or modifying tables?

    I see tables in Relationships window but there are no links so relationships are not established.

    I see [Event Procedure] in event properties but there is no VBA code. How did that happen?
    These are add on (new query returning adjusted data and querytable) I dont want to change, add or remove from existing tables and queries. Trying to create from what is there.

    I will try and let you know.

    As for the attachement it is probably corrupted!!!!

    Thanks

  9. #9
    Dano60 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2015
    Posts
    32

    Thumbs up

    Quote Originally Posted by Dano60 View Post
    These are add on (new query returning adjusted data and querytable) I dont want to change, add or remove from existing tables and queries. Trying to create from what is there.

    I will try and let you know.

    As for the attachement it is probably corrupted!!!!

    Thanks
    Thank you VIP the quote delimiters worked perfectly.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Making a button with a macro work
    By clrockwell15 in forum Macros
    Replies: 3
    Last Post: 06-23-2017, 12:51 PM
  2. Replies: 5
    Last Post: 05-23-2016, 09:15 AM
  3. Replies: 3
    Last Post: 02-23-2015, 11:57 AM
  4. Making Last Record Appear in Macro Button
    By wes9659 in forum Access
    Replies: 2
    Last Post: 04-14-2014, 01:34 PM
  5. Replies: 1
    Last Post: 12-21-2010, 09:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums