Parsing Data from Main Form&First SubForm into New Subform

G

Guest

Hi There,

I am trying to intergrate an existing database to some web work I have done.
I have A main form for building restaurant menus where items are selected
and stored into a subform. This data is for a printable version of the menus.
However I need to Re-append this data to another table for MySQL and PHP for
the website.

The new table is also a subform:

The Data I need to Parse is:

From the Main Form called 'CreateMenus':

Bar163_Location (Unbound Combo box with List Value) = on Main Form
Menu_Type
(Unbound Combo box with List Value) = on Main Form

From the first subform called 'CreateMenusSub' which has radio filters from
the main form

Desription (Bound to a Table through a Subform on the Main Form)
Price (Bound to a Table in through Subform on the Main Form)

The Table they are to be appended to are in another subform also shown on
the Main form and I have a control button which I am testing out for just the
two controls so far. However it asks me parameter values under the data I am
looking for. Probably because the controls are unbound on the main form. I
can type any data into the dialogue boxes and this then inserts into the New
Subform.

The Event on Click has this code:

Private Sub Upload_All_Click()

On Error GoTo Err_LocalError

DoCmd.SetWarnings False

Dim strSQL As String

strSQL = "INSERT INTO Menu_tbl (Bar163_LocationID, Menu_Type) VALUES ( " & _
Forms![CreateMenus]![Bar163_LocationID] & " , " &
Forms![CreateMenus]![Menu_Type] & " );"


DoCmd.RunSQL strSQL

Exit Sub

Err_LocalError:
MsgBox Err.Description
End Sub

Can anyone help here why the values are then encapsulated into another
Parameter I have to manually type in?

Thanks

Andi
 
G

Guest

Ok Getting Closer.

This works on from the main Form:

Private Sub Upload_All_Click()

Dim Location As String
Dim MenuType As String

Location = "Forms!CreateMenus.Bar163_LocationID"
MenuType = "Forms!CreateMenus.Menu_Type"
MenuItem = "Forms!CreateMenus!CreateMenusSub.Description"

On Error GoTo Err_LocalError

DoCmd.SetWarnings False

Dim strSQL As String

strSQL = "INSERT INTO Menu_tbl (Bar163_LocationID, Menu_Type,
Menu_Description) VALUES ( " & _
Location & " , " & MenuType & " , " & MenuItem & " , " & MenuItem & " );"

DoCmd.RunSQL strSQL
Forms!CreateMenus!menu_frm.Requery

Exit Sub

Err_LocalError:
MsgBox Err.Description
End Sub

This works ok. Now the problem is the other subform. It does not know which
record to select so have add another control called selected for web.

How do I get it to return all the values from the list where the item is
selected for web?

Would a do while loop safice?

Any help here much appreciated.

Thanks

Andi
 
G

Guest

Ok And here I am talking to myself again...

I now have this, but it's returning and Application or Object defined
Error... Haven't a clue what this means: Here's the code so far...

Private Sub Upload_All_Click()

On Error GoTo Err_LocalError

Dim strSQL As String
Dim Location As String
Dim MenuType As String
Dim MenuDescription As String
Dim Prices As Integer

DoCmd.SetWarnings False

Location = "Forms!CreateMenus.Bar163_LocationID"
MenuType = "Forms!CreateMenus.Menu_Type"

With [Forms]![CreateMenus]![CreateMenusSub].[Form]

Do While Not .EOF

MenuDescription = "SELECT MenuItems.Description, Menu2Items.SelectedForWeb
FROM Menu2Items INNER JOIN MenuItems ON Menu2Items.ItemID = MenuItems.ID
WHERE
(((MenuItems.Description)=[Forms]![CreateMenus]![CreateMenusSub]![Description])AND ((Menu2Items.SelectedForWeb)=True));"

strSQL = "INSERT INTO Menu_tbl (Bar163_LocationID, Menu_Type,
Menu_Description) VALUES ( " & _
Location & " , " & MenuType & " , " & MenuDescription & ");"

DoCmd.RunSQL strSQL

Loop

End With

Forms!CreateMenus!menu_frm.Requery

Exit Sub

Err_LocalError:
MsgBox Err.Description
End Sub



*****************************


Andi Lee Davis said:
Ok Getting Closer.

This works on from the main Form:

Private Sub Upload_All_Click()

Dim Location As String
Dim MenuType As String

Location = "Forms!CreateMenus.Bar163_LocationID"
MenuType = "Forms!CreateMenus.Menu_Type"
MenuItem = "Forms!CreateMenus!CreateMenusSub.Description"

On Error GoTo Err_LocalError

DoCmd.SetWarnings False

Dim strSQL As String

strSQL = "INSERT INTO Menu_tbl (Bar163_LocationID, Menu_Type,
Menu_Description) VALUES ( " & _
Location & " , " & MenuType & " , " & MenuItem & " , " & MenuItem & " );"

DoCmd.RunSQL strSQL
Forms!CreateMenus!menu_frm.Requery

Exit Sub

Err_LocalError:
MsgBox Err.Description
End Sub

This works ok. Now the problem is the other subform. It does not know which
record to select so have add another control called selected for web.

How do I get it to return all the values from the list where the item is
selected for web?

Would a do while loop safice?

Any help here much appreciated.

Thanks

Andi


Andi Lee Davis said:
Hi There,

I am trying to intergrate an existing database to some web work I have done.
I have A main form for building restaurant menus where items are selected
and stored into a subform. This data is for a printable version of the menus.
However I need to Re-append this data to another table for MySQL and PHP for
the website.

The new table is also a subform:

The Data I need to Parse is:

From the Main Form called 'CreateMenus':

Bar163_Location (Unbound Combo box with List Value) = on Main Form
Menu_Type
(Unbound Combo box with List Value) = on Main Form

From the first subform called 'CreateMenusSub' which has radio filters from
the main form

Desription (Bound to a Table through a Subform on the Main Form)
Price (Bound to a Table in through Subform on the Main Form)

The Table they are to be appended to are in another subform also shown on
the Main form and I have a control button which I am testing out for just the
two controls so far. However it asks me parameter values under the data I am
looking for. Probably because the controls are unbound on the main form. I
can type any data into the dialogue boxes and this then inserts into the New
Subform.

The Event on Click has this code:

Private Sub Upload_All_Click()

On Error GoTo Err_LocalError

DoCmd.SetWarnings False

Dim strSQL As String

strSQL = "INSERT INTO Menu_tbl (Bar163_LocationID, Menu_Type) VALUES ( " & _
Forms![CreateMenus]![Bar163_LocationID] & " , " &
Forms![CreateMenus]![Menu_Type] & " );"


DoCmd.RunSQL strSQL

Exit Sub

Err_LocalError:
MsgBox Err.Description
End Sub

Can anyone help here why the values are then encapsulated into another
Parameter I have to manually type in?

Thanks

Andi
Click to expand...
Click to expand...
 
G

Guest

Ok I'll Leave this last one here...

Private Sub Upload_All_Click()

On Error GoTo Err_LocalError

Dim strSQL As String
Dim Location As String
Dim MenuType As String
Dim MenuDescription As String
Dim Prices As Integer

DoCmd.SetWarnings False

Location = "Forms!CreateMenus.Bar163_LocationID"
MenuType = "Forms!CreateMenus.Menu_Type"

With [Forms]![CreateMenus].[menu_frm].[Form].Recordset

..MoveFirst

Do While Not .EOF
..Edit

MenuDescription = "SELECT MenuItems.Description, Menu2Items.SelectedForWeb
FROM Menu2Items INNER JOIN MenuItems ON Menu2Items.ItemID = MenuItems.ID
WHERE
(((MenuItems.Description)=[Forms]![CreateMenus]![CreateMenusSub]![Description])AND ((Menu2Items.SelectedForWeb)=True));"
strSQL = "INSERT INTO Menu_tbl (Bar163_LocationID, Menu_Type,
Menu_Description) VALUES ( " & _
Location & " , " & MenuType & " , " & MenuDescription & ");"

DoCmd.RunSQL strSQL

..Update
..MoveNext

Loop
End With

Forms!CreateMenus!menu_frm.Requery

Exit Sub

Err_LocalError:
MsgBox Err.Description
End Sub

Andi Lee Davis said:
Ok And here I am talking to myself again...

I now have this, but it's returning and Application or Object defined
Error... Haven't a clue what this means: Here's the code so far...

Private Sub Upload_All_Click()

On Error GoTo Err_LocalError

Dim strSQL As String
Dim Location As String
Dim MenuType As String
Dim MenuDescription As String
Dim Prices As Integer

DoCmd.SetWarnings False

Location = "Forms!CreateMenus.Bar163_LocationID"
MenuType = "Forms!CreateMenus.Menu_Type"

With [Forms]![CreateMenus]![CreateMenusSub].[Form]

Do While Not .EOF

MenuDescription = "SELECT MenuItems.Description, Menu2Items.SelectedForWeb
FROM Menu2Items INNER JOIN MenuItems ON Menu2Items.ItemID = MenuItems.ID
WHERE
(((MenuItems.Description)=[Forms]![CreateMenus]![CreateMenusSub]![Description])AND ((Menu2Items.SelectedForWeb)=True));"

strSQL = "INSERT INTO Menu_tbl (Bar163_LocationID, Menu_Type,
Menu_Description) VALUES ( " & _
Location & " , " & MenuType & " , " & MenuDescription & ");"

DoCmd.RunSQL strSQL

Loop

End With

Forms!CreateMenus!menu_frm.Requery

Exit Sub

Err_LocalError:
MsgBox Err.Description
End Sub



*****************************


Andi Lee Davis said:
Ok Getting Closer.

This works on from the main Form:

Private Sub Upload_All_Click()

Dim Location As String
Dim MenuType As String

Location = "Forms!CreateMenus.Bar163_LocationID"
MenuType = "Forms!CreateMenus.Menu_Type"
MenuItem = "Forms!CreateMenus!CreateMenusSub.Description"

On Error GoTo Err_LocalError

DoCmd.SetWarnings False

Dim strSQL As String

strSQL = "INSERT INTO Menu_tbl (Bar163_LocationID, Menu_Type,
Menu_Description) VALUES ( " & _
Location & " , " & MenuType & " , " & MenuItem & " , " & MenuItem & " );"

DoCmd.RunSQL strSQL
Forms!CreateMenus!menu_frm.Requery

Exit Sub

Err_LocalError:
MsgBox Err.Description
End Sub

This works ok. Now the problem is the other subform. It does not know which
record to select so have add another control called selected for web.

How do I get it to return all the values from the list where the item is
selected for web?

Would a do while loop safice?

Any help here much appreciated.

Thanks

Andi


Andi Lee Davis said:
Hi There,

I am trying to intergrate an existing database to some web work I have done.
I have A main form for building restaurant menus where items are selected
and stored into a subform. This data is for a printable version of the menus.
However I need to Re-append this data to another table for MySQL and PHP for
the website.

The new table is also a subform:

The Data I need to Parse is:

From the Main Form called 'CreateMenus':

Bar163_Location (Unbound Combo box with List Value) = on Main Form
Menu_Type
(Unbound Combo box with List Value) = on Main Form

From the first subform called 'CreateMenusSub' which has radio filters from
the main form

Desription (Bound to a Table through a Subform on the Main Form)
Price (Bound to a Table in through Subform on the Main Form)

The Table they are to be appended to are in another subform also shown on
the Main form and I have a control button which I am testing out for just the
two controls so far. However it asks me parameter values under the data I am
looking for. Probably because the controls are unbound on the main form. I
can type any data into the dialogue boxes and this then inserts into the New
Subform.

The Event on Click has this code:

Private Sub Upload_All_Click()

On Error GoTo Err_LocalError

DoCmd.SetWarnings False

Dim strSQL As String

strSQL = "INSERT INTO Menu_tbl (Bar163_LocationID, Menu_Type) VALUES ( " & _
Forms![CreateMenus]![Bar163_LocationID] & " , " &
Forms![CreateMenus]![Menu_Type] & " );"


DoCmd.RunSQL strSQL

Exit Sub

Err_LocalError:
MsgBox Err.Description
End Sub

Can anyone help here why the values are then encapsulated into another
Parameter I have to manually type in?

Thanks

Andi
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Ok Here is the final code:

Private Sub Upload_All_Click()

On Error GoTo Err_LocalError

Dim strSQL As String
Dim Location As String
Dim MenuType As String
Dim MenuDescription As String
Dim Prices



Me.Bar163_LocationID.SetFocus
If Me.Bar163_LocationID.Text = "" Then
msgbox "Please Select a Bar 163 Restaurant Location...", vbCritical, "Bar
163 Location Required"
Exit Sub
End If

Me.Menu_Type.SetFocus
If Me.Menu_Type.Text = "" Then
msgbox "Please Select a type of menu for the dishes you wish to add...",
vbCritical, "Menu Type Required"
Exit Sub
End If


With [Forms]![CreateMenus]![CreateMenusSub].[Form].Recordset

..MoveFirst

Do While Not .EOF

MenuDescription = "Forms!CreateMenus!CreateMenusSub!Description"
Prices = "Forms!CreateMenus!CreateMenusSub!EveningPrice"
Location = "Forms!CreateMenus.Bar163_LocationID"
MenuType = "Forms!CreateMenus.Menu_Type"

DoCmd.SetWarnings False

strSQL = "INSERT INTO Menu_tbl (Bar163_LocationID, Menu_Type,
Menu_Description, Price) VALUES ( " & _
Location & " , " & MenuType & " , " & MenuDescription & ", " & Prices &
");"

DoCmd.RunSQL strSQL

..MoveNext
Loop
End With
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filter subform with combo box on main form 1
Access Main form/Subform Navigation 0
Passing a value from subform or main form to subform query 4
ApplyFilter gives error only when subform is placed on main form 1
main form - with subform 1
List subform items on main form 2
Refreshing a subform 2
showing a subform based on the main form 5

Top