By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,761 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

Unbound form details to new record in table

P: 50
Hi experts. I'm stuck and could use your help ASAP again please. I have been trying to conquer this one for some time but being a relative VB novice, can't seem to crack it.

I have an unbound form with details of a product to add to a schedule in different text / combo boxes. I also have a series of dates with check boxes next to them. When I click an 'add to schedule' command button at the bottom of the form, i need a record to be added to a table for each date (along with its relative details inc 'machine' and 'operator' put in each field of the table) where the date and other information are fields only if a check box is ticked next to that date.

I have tried various methods such as 'setvalue' etc and encountered errors such as the table not being open. Is there a straight forward coding solution?

I hope you can help ASAP, many thanks in advance, OllyJ
Dec 17 '07 #1
Share this Question
Share on Google+
10 Replies


Rabbit
Expert Mod 10K+
P: 12,389
Why an unbound form instead of a bound form?
Dec 17 '07 #2

missinglinq
Expert 2.5K+
P: 3,532
Rabbit, as usual, is correct! Unbound forms are not for the faint hearted, and certainly not for novices! Go with a bound form!

Welcome to TheScripts!
Linq ;0)>
Dec 17 '07 #3

P: 50
I thought an unbound form because I am using it to prepare details to add to a table, which in turn will automatically fill a form bound to that table. It is an intermediary prep form. Even if it was a bound form however, i would still need a solution for what i'm struggling with (entering multiple records into a table from a form using a command button).

Please take a minute to read about my problem in more detail, it will help me explain where I am with it...

In more detail, my problem is that I am adding to a manufacturing schedule through an intermediary form (a 'quick add'). I have several details filled in such as a machine that the product should run on and an operator. Below these details are option buttons allowing the user to pick which week to add to schedule. When they do a series of relevant dates are visible with check boxes next to them. When the chosen dates are selected, and the command button ('add to schedule') pressed, I need for the details to be added to my schedule table (a record added for each date with all the previous details filling the remaining table fields).

It is probably a nice chunk of code that I need and I would be grateful to anyone who can help as soon as possible please...

Many thanks in advance

OllyJ
Dec 18 '07 #4

P: 50
Also......................(forgot to add on end of last reply (Apologies).............

Instead of the simple check boxes next to dates, a final option is to choose the dates through a multi select listbox which is sourced through a query runniong dates from 'today' to '3 months from today'......

I have tried mmccarthy's explanation (below) but I am not sure where I am supposed to state that it is to go to table 'tbl_schedule'.

Expand|Select|Wrap|Line Numbers
  1. Dim valSelect As Variant
  2. Dim strValue As String ' just used for the demonstration
  3.  
  4.     For Each valSelect In Me.listboxName.ItemsSelected
  5.         strValue = strValue & "'" & Me.listboxName.ItemData(valSelect) & "', "
  6.     Next valSelect
  7.  
  8.     ' to remove trailing comma
  9.     strValue = Left(strValue, Len(strValue)-2)
I have also tried the another method shown here to no avail (syntax erros etc that I cannot resolve)

I hope you guys can help me please,

OllyJ
Dec 18 '07 #5

Rabbit
Expert Mod 10K+
P: 12,389
I'd stick with the checkboxes, they're just easier to use if you're just starting out.
It's not too difficult, just do an:
Expand|Select|Wrap|Line Numbers
  1. If CheckboxName = True Then
  2.    DoCmd.RunSQL "INSERT INTO ..."
  3. End If
  4.  
Where the ... is the rest of your SQL insert statement. Do this for each checkbox.
Dec 18 '07 #6

missinglinq
Expert 2.5K+
P: 3,532
Using unbound forms is, as I've said, not for the faint hearted! It requires 10 times the time and effort of bound forms, and I've never feel the need! Here's an outline of an approach I've suggested a few times to other who have insisted on adding records en masse.
  1. Set up a separate, identical "holding table"
  2. Create a Continuous form using this holding table as its RecordSource
  3. Enter data into your records
  4. When everything is arranged as you want it, run an Append Query to add the records from the holding table to the permanent table
  5. Run an action query to delete the records from the holding table
Give this s try and let us know if you have troule with any of it.

Linq ;0)>
Dec 18 '07 #7

P: 50
Missinglinq, thank you very much for your comments.

I understand what you are saying regarding bound forms. I have looked at your holding table principle and I understand how it would benefit most, but I don't think it is suitable for me.... this is because, whilst it would save me a hell of a lot of time, trouble and probably headaches, my job is to make it that much easier for the user. If I did it this way the user would have to type in duplicate data for each record to be added.

My original plan was to have all the details that remain the same typed in once, and then check box the dates to add several details. This saving the user that much time despite making it more complex for me.

I know you are dead against unbound forms but I feel I have no choice so if you could help with this or point me in the direction of someone who can i will be extremely grateful!

Thank you for your time once again

OllyJ
Dec 19 '07 #8

P: 50
Rabbit, thank you for your comments.

Whilst I appreciate that check boxes are easier, I have since spoken to the potential end user again and I need a list box (to offer more dates) as well as check boxes (for the most common dates to add) If you have any example code for list box entries I would really appreciate it. *I know i'm a novice but it's looking like i'm going to have to learn more complex solutions quite quickly!*

Also, back to check boxes, I have tried adding the 'INSERT INTO' code but keep getting errors, the main one being 'Invalid Syntax', i used...

Expand|Select|Wrap|Line Numbers
  1. Private Sub AddtoSchedule_Click()
  2.         Dim valSelect As Variant
  3.         Dim strSQL As String
  4.  
  5.            For Each valSelect In Me.list0.ItemsSelected
  6.               strSQL = "INSERT INTO AddtoSchedule (Date) VALUES (" & 
  7.               Me.Date & ", " & Me.list0.ItemData(valSelect) & ")"
  8.            Next valSelect
  9.  
  10.         End Sub
I'm not sure what the " & Me.list0.ItemData(valSelect) & " does?????

Hope you can help again Rabbit, appreciate it

OllyJ
Dec 19 '07 #9

P: 50
Missinglinq, Rabbit.... after working on the problems that I have put to you this afternoon I think I have a solution to both using an unbound form and multi select from a listbox to add dates to a table.

These are the solutions I have come up with, please comment on how you think they will perform in a database situation ie performance etc.

To select checkboxes, and have multiple records entered directly into a table from an unbound form:
Expand|Select|Wrap|Line Numbers
  1. Private Sub CommandButtonName_Click()
  2.  
  3. Dim mySQL As String
  4.  
  5.     If Me.Roto_Check1 = True Then
  6.     mySQL = ("INSERT INTO TableName ([Date - Field1], [Field2], [Field3], [Field4], [Field5], [Field6]) VALUES (#" & Name of Object1 & "#, '" & Name of Object2 & "', '" & Name of Object3 & "', '" & Name of Object4 & "', '" & Name of Object5 & "', '" & Name of Object6 & "')")
  7.     DoCmd.RunSQL mySQL
  8.     End If
  9.  
  10.  
  11. End Sub
To use a listbox and have multiple selections added as multiple records to a table:
Expand|Select|Wrap|Line Numbers
  1. Private Sub CommandButtonName_Click()
  2.  
  3.         Dim valSelect As Variant
  4.         Dim strSQL As String
  5.  
  6.            For Each valSelect In Me.List0.ItemsSelected
  7.  
  8.             strSQL = "INSERT INTO TableName ([Date - Field1], [Stock Code - Field2]) VALUES (#" & Me.LISTNAME.ItemData(valSelect) & "#, '" & StockCode & "')"
  9.  
  10.             DoCmd.RunSQL strSQL
  11.  
  12.            Next valSelect
  13.  
  14.         End Sub

OllyJ
Dec 19 '07 #10

Rabbit
Expert Mod 10K+
P: 12,389
Looks good, thanks for posting your solution.
Dec 19 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.