Step through controls - append to table
Hi guys
I have a table, which is set out like this:
Month_Number Advisor Date_of_Month Shift_Status Experience_Level RecordID
(number) (text) (date) (text) (text) (autonumber) - PK
I have a form which I want to use to update this table.
The form has various combos and controls to ensure that the data entered is correct.
However, rather than selecting an individual day and saving the record, the form has 2 combos for each day - one for Experience and one for Shift_Status.
I want to click a button and the code do something like this:
(psuedo)
Record Advisor name from "cboAdvisor"
Record Month_Number from "cboMonth"
Record value of experience from "cboXX_Ex" 'where XX is the day from 01 - 31
Record value of shift status from "cboXX_Sh" 'where XX is the day from 01 - 31
Record value of Date_of_Month as "XX" from the combo box name
Save record
Loop to next pair of controls
Continue until all date and data updated.
Can anyone help...?
:-)
I have a table, which is set out like this:
Month_Number Advisor Date_of_Month Shift_Status Experience_Level RecordID
(number) (text) (date) (text) (text) (autonumber) - PK
I have a form which I want to use to update this table.
The form has various combos and controls to ensure that the data entered is correct.
However, rather than selecting an individual day and saving the record, the form has 2 combos for each day - one for Experience and one for Shift_Status.
I want to click a button and the code do something like this:
(psuedo)
Record Advisor name from "cboAdvisor"
Record Month_Number from "cboMonth"
Record value of experience from "cboXX_Ex" 'where XX is the day from 01 - 31
Record value of shift status from "cboXX_Sh" 'where XX is the day from 01 - 31
Record value of Date_of_Month as "XX" from the combo box name
Save record
Loop to next pair of controls
Continue until all date and data updated.
Can anyone help...?
:-)
ASKER
Bit of both - just to make it more difficult.
What I'd like to do is create the data in the table.
However, if "Advisor" and "Date_of_Month" in the table are the same as on the form, I want it to promt the user to either discard the change or UPDATE the value.
It's not the best design - but I need to build the form this way as the users are really just shaved chimps....
What I'd like to do is create the data in the table.
However, if "Advisor" and "Date_of_Month" in the table are the same as on the form, I want it to promt the user to either discard the change or UPDATE the value.
It's not the best design - but I need to build the form this way as the users are really just shaved chimps....
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Danny - just shoot the users and train some real chimps... productivity will go up, they'll be able to use a decent system and the canteen will only have to stock bananas.
s46
s46
... and it'll do wonders for your 'Investors in People' and 'Equal Opportunities' awards...
ASKER
Unfortunately, chimps need love too....
I think you've got the right idea, Sam.
i'll try it with a case statement and see what happens.
It will take me a little while (I have a meeting for an hour or so)
:-)
I think you've got the right idea, Sam.
i'll try it with a case statement and see what happens.
It will take me a little while (I have a meeting for an hour or so)
:-)
ASKER
I'm trying to use this insert statement in the CASE:
sSQL = "INSERT INTO tblRecords_Shift_Details ( Month_Number, Advisor, Shift_Status, Experience_Level, Date_of_Month ) "
sSQL = sSQL & "SELECT [Forms]![frmAdmin_Shifts]!
sSQL = sSQL & "FROM tblRecords_Shift_Details "
sSQL = sSQL & "GROUP BY [Forms]![frmAdmin_Shifts]!
DoCmd.RunSQL sSQL
It's trying to append 0 records - even though there's data in cboAdvisor, cbo01_Sh etc...
Any ideas...?
sSQL = "INSERT INTO tblRecords_Shift_Details ( Month_Number, Advisor, Shift_Status, Experience_Level, Date_of_Month ) "
sSQL = sSQL & "SELECT [Forms]![frmAdmin_Shifts]!
sSQL = sSQL & "FROM tblRecords_Shift_Details "
sSQL = sSQL & "GROUP BY [Forms]![frmAdmin_Shifts]!
DoCmd.RunSQL sSQL
It's trying to append 0 records - even though there's data in cboAdvisor, cbo01_Sh etc...
Any ideas...?
It will append nothing since you are using an INSERT query which is based on a table. Insert queries take this form:
INSERT INTO <destination table> ([fields]) SELECT <fields> FROM <source table>
If your source table has no data, it won't append anything. Think about the select part as a normal select query. Would that return any records? If not, there is no data to append.
To append a single row, use this:
INSERT INTO <destination table> (<fields>) VALUES(<values>)
So your statement should look something like this:
sSQL = "INSERT INTO tblRecords_Shift_Details ( Month_Number, Advisor, Shift_Status, Experience_Level, Date_of_Month ) "
sSQL = sSQL & "VALUES (" & [Forms]![frmAdmin_Shifts]!
DoCmd.RunSQL sSQL
I have made some assumptions about field data types - month being number, date_of_month being date and everything else being text. Note that if the month is stored in the table, you don't really need to store the date_of_month.
INSERT INTO <destination table> ([fields]) SELECT <fields> FROM <source table>
If your source table has no data, it won't append anything. Think about the select part as a normal select query. Would that return any records? If not, there is no data to append.
To append a single row, use this:
INSERT INTO <destination table> (<fields>) VALUES(<values>)
So your statement should look something like this:
sSQL = "INSERT INTO tblRecords_Shift_Details ( Month_Number, Advisor, Shift_Status, Experience_Level, Date_of_Month ) "
sSQL = sSQL & "VALUES (" & [Forms]![frmAdmin_Shifts]!
DoCmd.RunSQL sSQL
I have made some assumptions about field data types - month being number, date_of_month being date and everything else being text. Note that if the month is stored in the table, you don't really need to store the date_of_month.
ASKER
I've managed to change the statement to:
sSQL = "INSERT INTO tblRecords_Shift_Details ( Month_Number, Advisor, Shift_Status, Experience_Level, Date_of_Month ) SELECT [Forms]![frmAdmin_Shifts]!
And got it to paste in the details that i need.
The Date_of_Month value should be 01//09/2004 (when 09 is selected in cboMonth) (dd/mm/yyyy)
However, it pastes in as - 07/09/2425
I have no idea where this figure comes from...
sSQL = "INSERT INTO tblRecords_Shift_Details ( Month_Number, Advisor, Shift_Status, Experience_Level, Date_of_Month ) SELECT [Forms]![frmAdmin_Shifts]!
And got it to paste in the details that i need.
The Date_of_Month value should be 01//09/2004 (when 09 is selected in cboMonth) (dd/mm/yyyy)
However, it pastes in as - 07/09/2425
I have no idea where this figure comes from...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cheers guys (again....)
I'm gonna post another question - linked to this, but another syntax error on an UPDATE query.
(I will get the hang of this!)
Thanks again
I'm gonna post another question - linked to this, but another syntax error on an UPDATE query.
(I will get the hang of this!)
Thanks again
ThanX Danny!
God bless!
God bless!
ASKER
I've posted the new question on:
https://www.experts-exchange.com/questions/21139283/DLOOKUP-syntax.html
DLOOKUP and UPDATE
https://www.experts-exchange.com/questions/21139283/DLOOKUP-syntax.html
DLOOKUP and UPDATE
Are you trying to update date already in the table or append new data?
Sam