Results 1 to 12 of 12

Save values from a form to a table

  1. #1
    CoZak is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2018
    Posts
    15

    Unhappy Save values from a form to a table

    Hey guys, sorry to perturb with such a basic "task"...
    So I m saving data from road lighting measurements (i had another thread for relationships but some guys managed to help me out (thanks d00ds))
    For the same Bound, Line and Kilometric Point (KP) there can be many measurements, so in order not to have to type recurrent data multiple times just for 1 measurement I wanted to make a form and update the form from there.


    Problem is that i m not used to VBA and even less to VBA on access

    Do I still have to dim every string even if I have formated it in properties? (pic related) like is Dim txt_kp as Integer etc etc ... needed
    Then how can I populate my table with value from the form?
    I guess an If function could work but problem is i cant use the "Cells (emptyrow,1).Value=txt_kp" as in excel correct?

    Click image for larger version. 

Name:	code.jpg 
Views:	32 
Size:	136.0 KB 
ID:	34358

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    6,446
    Cells (emptyrow,1).Value=txt_kp IS ONLY for excel

    you dont have to DIM variables, everything can work as variant
    you DO have to Dim if the OPTION EXPLICIT is used in the module.

  3. #3
    CoZak is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2018
    Posts
    15
    Hmm ok as I expected...
    What could I use instead of 'Cells (emptyrow,1).Value=txt_kp
    that could work for access?

  4. #4
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    615
    Declare your strings, numbers matter. When you concatenate variants the compiler(i think) must preprocess which data to cast to, avoid concatenating a variant.

    If your form is bound to a table, with bound fields, you can click the Triangle on the Record Selector. If its not bound you will need to build a SQL statement to update the information.
    Patience serves as a protection against wrongs as clothes do against cold.

  5. #5
    Beetle is online now Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    193
    The fact that you are trying to jump through VBA hoops to write data to your table indicates to me that you may have a structure problem. You mentioned that you are having to repeatedly enter the same data, but you don't specifically say which part of the data is repeating. I'm going to assume that you mean you are having to re-enter the Bound, Line and KP each time you enter a new measurement. This data should not be in the same table as the measurements, it should be in a parent table, with measurements in a child table. The only value from the parent table that should be stored in the measurements table is the primary key value. Then you use a main form/sub form for data entry, where the user selects the appropriate B/L/KP in the main form and enters the measurements in the sub form. No need for some VBA kludge to get data in your table. I have seen your other thread, and you definitely had some structure problems in the sample db's you posted there at the time, but you said in post #1 you had that worked out, so I don't know where you table design stands at the moment (other than it sounds like you may still have some issues).
    Sean Bailey

    Dim Thirsty As String
    Thirsty = "Insert Into Glass (Select Beer From Fridge)"
    DoCmd.Execute Thirsty, dbPourAnotherOnError

  6. #6
    CoZak is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2018
    Posts
    15
    Hey Beetle, so you re saying there should be a parent table with only B/L/KP and the primary key and then a child table with no primary key but the key from the parent table? But then i dont really know how to do: "Then you use a main form/sub form for data entry, where the user selects the appropriate B/L/KP in the main form and enters the measurements in the sub form".

    Road_Marking_Database_15.zipI ll try to do what you suggest and see how it goes.

  7. #7
    Beetle is online now Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    193
    The child table should still have it's own primary key field, but then you would also store the primary key value from the parent table as a foreign key.
    Sean Bailey

    Dim Thirsty As String
    Thirsty = "Insert Into Glass (Select Beer From Fridge)"
    DoCmd.Execute Thirsty, dbPourAnotherOnError

  8. #8
    CoZak is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2018
    Posts
    15
    Thanks Beetle, much appreciated, upvoted! I did what u told me and it s great I even put bound comboboxes to make it look better you can take a look here.
    Road_Marking_Database_22.zip

    One quick question, I wanna link application dates with paint batches (jct table), like if an area was painted on a date between(## and ##) it would have the paint_batch_ID of that line, problem is I cant refer to range() the same way I can in Excel, so i m kinda stuck, atm I use
    PaintBatch_ID: IIf([Elexar].[Application Date]<=#18-May-18# And [Elexar].[Application Date]>=#07-Jan-17#,1,2)
    But it s not good because if i add new dates are change these it wouldnt be taken into account. I could nest the IIFs in order to have it all in one line in I can manage my first idea

    I ll prly find a way but you guys could have an idea much quicker!

    Thanks for the help again Beetle!

  9. #9
    Beetle is online now Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    193
    What are the details of the scenario where you want to relate Application Date to a paint batch? I'll guess from your previous post that it has something to do with table Elexar, but you also have three other tables that have a field named Application Date. Also, I get the impression that the Elexar table stores imported data. So, are you trying to do this during import, or at the form level based on user input, or some other scenario?
    Sean Bailey

    Dim Thirsty As String
    Thirsty = "Insert Into Glass (Select Beer From Fridge)"
    DoCmd.Execute Thirsty, dbPourAnotherOnError

  10. #10
    CoZak is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2018
    Posts
    15
    Well if I can do that during import that d be great but i m not sure I can do it. Yes Elexar is imported data as you can see in the form I ve created a button for it
    The application date on "Elexar" and "Application condition" is the same, it is imported in elexar but put in manually for application conditions. I just want to link the application date to the batch of paint that was applied (table paint batch as 1st date of application and last date of applicaiton)

  11. #11
    Beetle is online now Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    193
    Not sure if this is exactly what you are looking for, but I have modified your Application Conditions form. I added a subform that will return the associated paint batch based on the Application Date. This may at least point you in a direction.
    See attached.
    Attached Files Attached Files
    Sean Bailey

    Dim Thirsty As String
    Thirsty = "Insert Into Glass (Select Beer From Fridge)"
    DoCmd.Execute Thirsty, dbPourAnotherOnError

  12. #12
    CoZak is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2018
    Posts
    15
    Hey Beetle, definitely point me in the right direction
    Thank you very much!

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

Similar Threads

  1. Save form textbox values
    By NISMOJim in forum Programming
    Replies: 4
    Last Post: 07-30-2015, 03:48 AM
  2. Replies: 11
    Last Post: 07-24-2012, 06:50 PM
  3. Replies: 2
    Last Post: 01-09-2012, 07:15 AM
  4. Replies: 9
    Last Post: 01-20-2011, 01:22 PM
  5. Replies: 3
    Last Post: 04-04-2010, 04:26 PM

Posting Permissions

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