Tech Off Thread

15 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Access Forms

Back to Forum: Tech Off
  • User profile image
    W3bbo

    Disclaimer: I've no idea how to do Access forms.

    I've got an Access database (MDB, not ADP) with a few tables:

    Rounds(ID, Name, Date)
    Entries(ID, Name, Round)
    Participants(ID, Name)
    Awards(ID, Name, PointDifference)
    EntryAwards(Entry, Award)

    I've already got the SQL for working with it fine, I just want to get a working Access form that let's me add entries to the database.

    A form with a drop down at the top that lets me select the Competition Round that I want to add the Competition Entry to (the value should be persisted between clicks of the "Insert" button)

    A simple list-box with two buttons "Add" and "Remove" which allows me to grant this Competition Entry an award (clicking "Add" would show a menu with possible awards to add, if that isn't possible then just show a child form with a listing of awards), the "Remove" button removes the selected award from the list.

    The form has two other fields, "Entry Name" and "Participant", "Participant" is a drop-down list which allows me to select a given participant, this...like the "Round" drop-down should persist between Insert operations.

    And a "New" button next to the "Participant" drop-down that opens a new form with fields for inserting a new participant. Once entered, the drop-down in the parent form should update itself.

    This isn't a commerical nor homework project (it's something for this competition I run), so don't flame me for asking you to do my work for me (I'm not anyway, I just want to know how to do it Smiley )

  • User profile image
    Red5

    When I was working in Access about 5 years ago there were wizards installed to help with the things you are asking about.  Have you checked those out yet?

  • User profile image
    W3bbo

    Red5 wrote:
    When I was working in Access about 5 years ago there were wizards installed to help with the things you are asking about.  Have you checked those out yet?


    I tried those, but they seem limited to simple data operations.

    But nevermind, I'm doing it manually, I think I've got the hang of it

  • User profile image
    W3bbo

    I've decided to change the "Awards" thing into a listbox containing all the awards in the database. Awards are chosen by simply selecting all applicable awards.

    Now to figure out the insertion procedure.

  • User profile image
    Tensor

     Give you a clue - it begins "INSERT INTO.." Wink

  • User profile image
    W3bbo

    Tensor wrote:
     Give you a clue - it begins "INSERT INTO.." Wink


    Heh

    I actually meant how to get the Primary Key of selected items from databound controls.

    BTW Tensor, your email address on your business card doesn't work

  • User profile image
    Tensor

    W3bbo wrote:
    
    Tensor wrote:  Give you a clue - it begins "INSERT INTO.." Wink


    Heh

    I actually meant how to get the Primary Key of selected items from databound controls.

    BTW Tensor, your email address on your business card doesn't work


    Hmm - thats odd. I just had a major panic that maybe I had typod it on the business card, but if I mail it reads correctly and I can send to it myself from gmail....

  • User profile image
    W3bbo

    Tensor wrote:
    Hmm - thats odd. I just had a major panic that maybe I had typod it on the business card, but if I mail it reads correctly and I can send to it myself from gmail....


    I meant doesn't work from Messenger

  • User profile image
    W3bbo

    So anyway:

    DoCmd.RunSql(Command As String) cannot execute SELECT statements.

    ...does anyone know how to do SELECT from within an Access VBA?

  • User profile image
    Tensor

    W3bbo wrote:
    
    Tensor wrote: Hmm - thats odd. I just had a major panic that maybe I had typod it on the business card, but if I mail it reads correctly and I can send to it myself from gmail....


    I meant doesn't work from Messenger




    Ahhhh panic over. thats because I use my gmail account for my passport. I allready changed hat in the last 6 months and dont want to cause further confusion. Feel free to add me using the gmail account you will have for me Smiley

  • User profile image
    Tensor

    What version fo access are you using? In the olden days of yore, you would open a recordset. I think that was DAO though - its probably all ADO now. You might want to look up recordsets though.

    Yeah that was it. I would be somthing like

    dim rs as recordset
    set rs = currentdb.openrecordset(sql)

    then iterate away.

  • User profile image
    Red5

    W3bbo wrote:
    So anyway:

    DoCmd.RunSql(Command As String) cannot execute SELECT statements.

    ...does anyone know how to do SELECT from within an Access VBA?


    Simple way:
    Create a query called qryTest

    Acess Basic code:

    Private Sub Command0_Click()
        DoCmd.OpenQuery "qryTest", acViewNormal,  acReadOnly
    End Sub

    It's up to you to add parameters if needed.
    DAO is your friend.

  • User profile image
    W3bbo

    Red5 wrote:
    
    W3bbo wrote:So anyway:

    DoCmd.RunSql(Command As String) cannot execute SELECT statements.

    ...does anyone know how to do SELECT from within an Access VBA?


    Simple way:
    Create a query called qryTest

    Acess Basic code:

    Private Sub Command0_Click()
        DoCmd.OpenQuery "qryTest", acViewNormal,  acReadOnly
    End Sub

    It's up to you to add parameters if needed.
    DAO is your friend.


    Well, here's my SQL

    "SELECT Top 1 ID FROM Entries"

    How do I get the single result into an Integer variable? OpenQuery looks like a Sub, not a Function

  • User profile image
    W3bbo

    Okay, I've got it all now.

    This is my Execute function:

    Public Function Execute(Optional Cmd As DoCmd) As ADODB.Recordset
       
       
        ' Execute = Cmd.RunSQL(strCommand)
        ' Cmd.RunSQL strCommand
       
        Dim rs As ADODB.Recordset
        Set rs = CurrentDb.OpenRecordset(strCommand)
       
        Set Execute = rs
       
    End Function

    (Ignore the commented out bits)

    When strCommand = "INSERT INTO Entries (Title, Participant, Round) VALUES ('Niehs Twow', 37, 4)" the operation fails throwing Error code 3219 ("Invalid Operation")


    What am I doing wrong?

  • User profile image
    W3bbo

    Solved, I forgot that the .OpenRecordset function only works on SELECTs.

    I solved it by executing DoCmd.RunSQL when performing any non-SELECT operation.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.