Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Dec 2003
    Location
    Oxford, England
    Posts
    38

    Question Unanswered: Creating new records populated from a value in a combo

    Hi

    Can anyone help?

    Below is a picture of what i want to do.

    I have 3 cascading combo boxes (which work fine), i want the user to be able to select the values in the combo boxes and then press a button and have a new record created in the subform below with the values from the combo boxes.

    Does anyone know how i can go about this?

    Luke
    Attached Thumbnails Attached Thumbnails pictureofproblem.bmp  

  2. #2
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: Creating new records populated from a value in a combo

    Originally posted by Lukelrc
    Hi

    Can anyone help?

    Below is a picture of what i want to do.

    I have 3 cascading combo boxes (which work fine), i want the user to be able to select the values in the combo boxes and then press a button and have a new record created in the subform below with the values from the combo boxes.

    Does anyone know how i can go about this?

    Luke
    Sure Luke...

    Just execute an INSERT SQL statement (or create a recordset object and use .AddNew) in the Button's On Click event procedure, to append the new record to the subform's recordsource object... Then refresh the recordset in the subform...

    If you need more specific's, post back with more information (like field names, table names, control names, etc...)

    HTH

  3. #3
    Join Date
    Dec 2003
    Location
    Oxford, England
    Posts
    38
    Oh, that would be very helpful!

    If you could point me in the right direction with one example i should be able to do it myself, i'm new to SQL but i need to learn.

    Combo: 'Combo0'
    Subform: 'plannedoutputs subform'
    Field (for data to go into): 'Objective' from table 'outputsplan'

    Do you need more info??

    Thanks in advance!

    Luke

  4. #4
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Okay Luke...
    Since you want to learn and just want an example, I took one from one of my databases...

    First of all... The INSERT INTO SQL statement has two different forms... to append a group of records, or to append a single record... The basic syntax for appending a single record, which is what we're doing here, is

    INSERT INTO tablename (field names in table separated by commas)
    VALUES (in the same order as the above fields; also separated by commas);

    Simple example from Access Help is:

    INSERT INTO Employees (FirstName,LastName, Title)
    VALUES ('Harry', 'Washington', 'Trainee');

    (Notice the single quotes around string values... You would need # signs around date values (date data types) and nothing around numeric values... The important thing is to use the data type of the field you are putting the value INTO...)

    ............. k.... enough of that.... Here's the example of what's behind my Save Record button on a form of mine...

    Private Sub cmdSave_Click()
    Dim db As DAO.Database 'declare object variable
    Dim strSQL As String 'declare string variable

    Set db = CurrentDb 'set object value and allocate memory

    'create the SQL statement in a string using concatenation...
    strSQL = "INSERT INTO MTDINT" & _
    " ( [RDATE], [ACCOUNT], [CURR], [SECURITY], [DESC], [MATURITY], [POSITION], " & _
    "[YIELD1], [INT], [No of days], [DINT], [FLAG2] )" & _
    " VALUES ( '" & Me!txtRDate & "', '" & Me!cboAccount & "', '" & Me!cboCurrency & "', '" & _
    Me!txtSecurity & "', '" & UCase(Me!txtDescription) & "', '" & Me!txtMaturity & "', " & Me!txtPosition & _
    ", " & Me!txtYield1 & ", " & Me!txtInt & ", " & Me!txtNoOfDays & ", " & Me!txtDINT & ", '" & _
    Me!txtFlag2 & "');"

    'execute the SQL action query
    db.Execute strSQL

    MsgBox "Record has been appended to the MTDINT table..."

    End Sub


    I know it's not formatted all neat on here, but if you copy and paste it to Word or something you'll be able to break it down and see what's going on... The ampersand is the concatenation character in VBA and the underscore is the line extension character... Basically all I'm doing is making one longggg string with the SQL statement inside... It's adding one record to one table from controls on my form... That's all...

    For you your SQL will look something like...

    "INSERT INTO outputsplan (Objective) " & _
    "VALUES (" & Me!Combo0 & ");"

    but you'll have to extend the list of fields inside the brackets for the other two combo boxes, etc...

    Ohhh and since you want to see this new record added to the subform, add a statement to requery the subform control...

    Hope I haven't confused you too much here... I really do want to help...

    Have a great day!

  5. #5
    Join Date
    Dec 2003
    Location
    Oxford, England
    Posts
    38
    Wow!!!

    Thanks a lot! I'm about to go home now but i'll get stuck into this first thing tomo. Almost looking forward to it!

    Luke
    :-)

  6. #6
    Join Date
    Dec 2003
    Location
    Oxford, England
    Posts
    38
    Hi, Thanks again for the help.

    I've done what you said. Initally i got lost of debugger errors about referances and the like, but i seem to have sorted them. The only thing is that the button doesn't work, i.e nothing happens. Can you see any glairing errors in my code:

    Private Sub Command40_Click()
    Dim db As DAO.Database 'declare object variable
    Dim strSQL As String 'declare string variable

    Set db = CurrentDb 'set object value and allocate memory

    'create the SQL statement in a string using concatenation...
    strSQL = "INSERT INTO OutputsPlan (Objective) " & _
    "VALUES (" & Me![Limiter form].[Combo0] & ");"
    'execute the SQL action query
    db.Execute strSQL

    [plannedoutputs SubForm].Requery
    End Sub

    ??

    Thanks in advance

    Luke

  7. #7
    Join Date
    Dec 2003
    Location
    Oxford, England
    Posts
    38
    Agh!

    Now it says 'object doesn't support this property method' and highlights this:

    strSQL = "INSERT INTO OutputsPlan (Objective) " & _
    "VALUES (" & Me![Limiter form].[Combo0] & ");"

    and i didn't even change anything?

  8. #8
    Join Date
    Dec 2003
    Location
    Oxford, England
    Posts
    38
    Agh!

    Now it says 'object doesn't support this property method' and highlights this:

    strSQL = "INSERT INTO OutputsPlan (Objective) " & _
    "VALUES (" & Me![Limiter form].[Combo0] & ");"

    and i didn't even change anything?

  9. #9
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by Lukelrc
    Agh!

    Now it says 'object doesn't support this property method' and highlights this:

    strSQL = "INSERT INTO OutputsPlan (Objective) " & _
    "VALUES (" & Me![Limiter form].[Combo0] & ");"

    and i didn't even change anything?

    Wow... Just realized I never logged out last night... Oops... lol

    k... Let's figure this out... First of all you need to check your references and see if anything is missing...

    Open the form module... Go to Tools --> References... When it opens look and see if there is anything checked that says MISSING in front of it...

    Don't worry Luke... We'll get it working...

  10. #10
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by Lukelrc
    Agh!

    Now it says 'object doesn't support this property method' and highlights this:

    strSQL = "INSERT INTO OutputsPlan (Objective) " & _
    "VALUES (" & Me![Limiter form].[Combo0] & ");"

    and i didn't even change anything?
    Oops again... I forgot to tell you what to do if a reference IS missing... lol... (I must need more coffee... lol)

    If you see one that says missing... remove the check mark and close that window... Go to the Debug menu and click Compile Loaded Modules... Then check the Reference list again and see if Access has assigned what you need... Most of the time it's automatic... but there are exceptions... I don't know what version of Access you're running but you may have to explicitly set references to DAO or Office objects... etc...

  11. #11
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by Lukelrc
    Agh!

    Now it says 'object doesn't support this property method' and highlights this:

    strSQL = "INSERT INTO OutputsPlan (Objective) " & _
    "VALUES (" & Me![Limiter form].[Combo0] & ");"

    and i didn't even change anything?
    Hey Luke...

    Check that [Limiter form] is the name of the subform CONTROL... not the subform name itself... That might be the problem... I'm looking at this thing and thinking... "there are no objects here... what the...?" lol... and that came to mind... Check that name... It's a common error...

  12. #12
    Join Date
    Dec 2003
    Location
    Oxford, England
    Posts
    38
    Thanks Trudi,

    I mangaged to sort that referance thing out i think. I went to tools, referances and checked the Microsoft DAO. At least it's stopped asking me to set the referance. Its just that the button does nothing. At all. Can you see if i have got the code right?


    Private Sub Command40_Click()
    Dim db As DAO.Database 'declare object variable
    Dim strSQL As String 'declare string variable

    Set db = CurrentDb 'set object value and allocate memory

    'create the SQL statement in a string using concatenation...
    strSQL = "INSERT INTO OutputsPlan (Objective) " & _
    "VALUES (" & Me.[Limiter form]![Combo0] & ");"
    'execute the SQL action query
    db.Execute strSQL

    [plannedoutputs SubForm].Requery
    End Sub


    Many Thanks

    Luke.

  13. #13
    Join Date
    Dec 2003
    Location
    Oxford, England
    Posts
    38
    ah! Limiter for is the name of the subform! Maybe thats it! Let me check it out.

    Luke

  14. #14
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by Lukelrc
    Thanks Trudi,

    I mangaged to sort that referance thing out i think. I went to tools, referances and checked the Microsoft DAO. At least it's stopped asking me to set the referance. Its just that the button does nothing. At all. Can you see if i have got the code right?


    Private Sub Command40_Click()
    Dim db As DAO.Database 'declare object variable
    Dim strSQL As String 'declare string variable

    Set db = CurrentDb 'set object value and allocate memory

    'create the SQL statement in a string using concatenation...
    strSQL = "INSERT INTO OutputsPlan (Objective) " & _
    "VALUES (" & Me.[Limiter form]![Combo0] & ");"
    'execute the SQL action query
    db.Execute strSQL

    [plannedoutputs SubForm].Requery
    End Sub


    Many Thanks

    Luke.
    No problem...

    First thing I'd check is that you have "[Event Procedure]" selected in the On Click event for that button...

    I don't see anything wrong with the code... Do you know how to set a break point and step through code?

  15. #15
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by Lukelrc
    ah! Limiter for is the name of the subform! Maybe thats it! Let me check it out.

    Luke
    Crossing my fingers...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •