Unbound form details to new record in table

[OllyJ]

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

[Rabbit]

Why an unbound form instead of a bound form?

[missinglinq]

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)>

[OllyJ]

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

[OllyJ]

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'.

Dim valSelect As Variant
Dim strValue As String ' just used for the demonstration
 
    For Each valSelect In Me.listboxName.ItemsSelected
        strValue = strValue & "'" & Me.listboxName.ItemData(valSelect) & "', "
    Next valSelect
 
    ' to remove trailing comma
    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

[Rabbit]

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:

If CheckboxName = True Then
   DoCmd.RunSQL "INSERT INTO ..."
End If
 

Where the ... is the rest of your SQL insert statement. Do this for each checkbox.

[missinglinq]

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)>

[OllyJ]

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

[OllyJ]

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...

Private Sub AddtoSchedule_Click()
        Dim valSelect As Variant
        Dim strSQL As String
 
           For Each valSelect In Me.list0.ItemsSelected
              strSQL = "INSERT INTO AddtoSchedule (Date) VALUES (" & 
              Me.Date & ", " & Me.list0.ItemData(valSelect) & ")"
           Next valSelect
 
        End Sub

I'm not sure what the " & Me.list0.ItemData(valSelect) & " does?????

Hope you can help again Rabbit, appreciate it

OllyJ

[OllyJ]

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:

Private Sub CommandButtonName_Click()
 
Dim mySQL As String
 
    If Me.Roto_Check1 = True Then
    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 & "')")
    DoCmd.RunSQL mySQL
    End If
 
 
End Sub

To use a listbox and have multiple selections added as multiple records to a table:

Private Sub CommandButtonName_Click()
 
        Dim valSelect As Variant
        Dim strSQL As String
 
           For Each valSelect In Me.List0.ItemsSelected
 
            strSQL = "INSERT INTO TableName ([Date - Field1], [Stock Code - Field2]) VALUES (#" & Me.LISTNAME.ItemData(valSelect) & "#, '" & StockCode & "')"
 
            DoCmd.RunSQL strSQL
 
           Next valSelect
 
        End Sub

OllyJ

[Rabbit]

Looks good, thanks for posting your solution.