Page 6 of 6 FirstFirst 123456
Results 76 to 86 of 86
  1. #76
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    Hmmm...not as smart, nor as stoopid as I thought I was. First, figured out the combo box puzzle when I discovered that I could (had to) modify the Row Source field to look for the correct data as a query. So that question can be checked off the list. But now I'm trying to make entries into the frmCellFreezerLog and understand that I still have some work to do (i.e. what I've done doesn't work). Specifically, here's how I tried to modify Steve's code:

    Code:
        ' create Append SQL string
        sSQL = "INSERT INTO tblCellFreezerLog ( cellassayFK, freezerFK, FreezeDate, Cane, Box, BoxColumn, BoxRow, BarcodeID )"
        sSQL = sSQL & " VALUES(" & Me.cboHostCell & ", " & Me.cboFusionPartner & ", " & Me.cboKinase & ", " & Me.cboAllele & ", " & Me.cboDerivationMethod & ", "
        sSQL = sSQL & Me.cboFreezer & ", #" & Me.tbFreezeDate & "#, " & Me.tbCane & ", " & Me.tbBox & ", " & Me.tbColumn & ", '" & UCase(Me.tbRow) & "', '" & Me.tbBarcode & "');"
        'Debug.Print sSQL
    What I want is for Access to look up the values entered for combo boxes HostCell, FusionPartner, Kinase, Allele and DerivationMethod and from those combined values return the value for the cellassayFK (which is now in "qryCellAssay") and insert it into tblCellFreezerLog. As written, Access has no desire to do that and simply tells me I'm an idiot.

    My guess at the solution: create a single text box that returns a lookup value from "qryCellAssay" based on all of the combo boxes specified above. That text box would be hidden on the form so the end user could not see it, but then would store the value needed by the Cell Freezer Log table.



    On the right track?

  2. #77
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Remove this quote and see what gets printed to the immediate window.
    'Debug.Print sSQL

  3. #78
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    Quote Originally Posted by orange View Post
    Remove this quote and see what gets printed to the immediate window.
    'Debug.Print sSQL
    Runtime error 3346

    Number of query values and destination fields are not the same

    Presumably because I'm asking Access to look up several different values and then store only one in the destination field?

  4. #79
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    I looked more closely at the SQL syntax Steve put together and tried this:

    Code:
    Private Sub cmdSaveNext_Click()
        Dim sSQL As String
    
    
        ' need to check the controls on the form to ensure controls are not null
    
    
        ' create Append SQL string
        sSQL = "INSERT INTO tblCellFreezerLog ( cellassayFK, freezerFK, FreezeDate, Cane, Box, BoxColumn, BoxRow, BarcodeID )"
        sSQL = sSQL & " WHERE cellassayFK = " & Me.cboHostCell & " AND " & Me.cboFusionPartner & " AND " & Me.cboKinase & " AND " & Me.cboAllele & " AND " & Me.cboDerivationMethod & "'"
        sSQL = sSQL & " VALUES(" & Me.cboHostCell & ", " & Me.cboFusionPartner & ", " & Me.cboKinase & ", " & Me.cboAllele & ", " & Me.cboDerivationMethod & ", "
        sSQL = sSQL & Me.cboFreezer & ", #" & Me.tbFreezeDate & "#, " & Me.tbCane & ", " & Me.tbBox & ", " & Me.tbColumn & ", '" & UCase(Me.tbRow) & "', '" & Me.tbBarcode & "');"
        Debug.Print sSQL
    
    
        CurrentDb.Execute sSQL, dbFailOnError
    
    
        DoEvents
    
    
        Me.tbBarcode = vbNullString
        Me.tbBarcode.SetFocus
    
    
        Call IncrementSlot
    End Sub
    I was foolishly optimistic. My reward was "Runtime error 3134 Syntax error in INSERT INTO statement".

    If I debug, then it points to this statement:

    Code:
    CurrentDb.Execute sSQL, dbFailOnError
    The voices warned me this would happen.

  5. #80
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It would be helpful if you would provide
    - a jpg of the design of your tblCellFreezerLog so we can see the fields involved
    - a jpg of the form

    I have not seen your latest database.

    When you build an INSERT query via vba, it can be tricky. Dates need to be enclosed in # and strings need to be enclosed in quotes.
    It appears you are trying to use this general format for SQL INSERT:

    INSERT INTO table_name (column1,column2,column3,...)
    VALUES (value1,value2,value3,...);
    If you are trying to concatenate values from several combo boxes into a single field value, such as:

    Me.cboOne with selected values "A" and Me.cboTwo "B" and me.cboThree "C" to build a value like ABC, then
    you need something along this line

    Me.cboOne & Me.CboTwo & Me.cboThree

    From a best practices view, it is always a good idea to use the debug.print on the SQL string you are building. You can review the print for syntax errors, and you can copy the print and test it using the SQL view of the query designer.

    An earlier suggestion re the freezerlog (cryospace) was -because you have a finite number of locations - to set it up much like a
    set of "pidgeon holes". Identify all freezers, canes...initially, then use Update queries for most maintenance. It was an alternative for consideration.

  6. #81
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    First - thanks to my two colleagues Agent Orange and Dr. Velcro for their recent contributions
    Now that I am back in my chair......

    From Post #76
    Quote Originally Posted by Accessed View Post
    ' create Append SQL string
    sSQL = "INSERT INTO tblCellFreezerLog ( cellassayFK, freezerFK, FreezeDate, Cane, Box, BoxColumn, BoxRow, BarcodeID )"
    sSQL = sSQL & " VALUES(" & Me.cboHostCell & ", " & Me.cboFusionPartner & ", " & Me.cboKinase & ", " & Me.cboAllele & ", " & Me.cboDerivationMethod & ", "
    sSQL = sSQL & Me.cboFreezer & ", #" & Me.tbFreezeDate & "#, " & Me.tbCane & ", " & Me.tbBox & ", " & Me.tbColumn & ", '" & UCase(Me.tbRow) & "', '" & Me.tbBarcode & "');"
    'Debug.Print sSQL
    From Post #78
    Quote Originally Posted by Accessed View Post
    Runtime error 3346

    Number of query values and destination fields are not the same

    Presumably because I'm asking Access to look up several different values and then store only one in the destination field?
    Kinda.
    In this part of the SQL string
    Code:
    sSQL = "INSERT INTO tblCellFreezerLog ( cellassayFK, freezerFK, FreezeDate, Cane, Box, BoxColumn, BoxRow, BarcodeID )"
    there are 8 fields that you want to insert data into in table "tblCellFreezerLog".
    In this part of the SQL string
    Code:
    sSQL = sSQL & " VALUES(" & Me.cboHostCell & ", " & Me.cboFusionPartner & ", " & Me.cboKinase & ", " & Me.cboAllele & ", " & Me.cboDerivationMethod & ", "
        sSQL = sSQL & Me.cboFreezer & ", #" & Me.tbFreezeDate & "#, " & Me.tbCane & ", " & Me.tbBox & ", " & Me.tbColumn & ", '" & UCase(Me.tbRow) & "', '" & Me.tbBarcode & "');"
    you have 12 values.
    The number of fields and the number of values MUST match.


    --------------------------------------
    In my defense of the Append query and Demo1, the table structure was
    Click image for larger version. 

Name:	Simple.jpg 
Views:	29 
Size:	44.4 KB 
ID:	25047

    Now the structure has morphed into
    Click image for larger version. 

Name:	Complex.jpg 
Views:	29 
Size:	73.5 KB 
ID:	25048

    Note the changes in the tables with the rec oval.

    Click image for larger version. 

Name:	Table1.jpg 
Views:	29 
Size:	16.0 KB 
ID:	25049

    Where was I going with this??????

    Oh, now I remember.....
    In the form for "tblCellFreezerLog", you could have a combo box for the field "cellassayFK" that displays "ALK/L1196M/BCR/BAF3", but stores the PK value from the table "tblCellAssayLog". (I think that is what you were trying to do)
    It just depends on the query for the combo box row source property.

    What say you?? Clear as mud?

  7. #82
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    muddy.. but confusing to me--but I have not stayed real close.
    In the tblCellFreezerLog jpg in your #81) cellassayFK is datatype Number. What exactly is presented if you show "ALK/L1196M/BCR/BAF3"?

    I realize you want it clear for users, but it looks/feels confusing to me. But, I'm not a user of the system.

  8. #83
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Sorry. Let me try to be less muddy.....

    Quote Originally Posted by orange View Post
    muddy.. but confusing to me--but I have not stayed real close.

    I realize you want it clear for users, but it looks/feels confusing to me. But, I'm not a user of the system.
    I'm not a user either..... I got lost after the 3rd post.... or was it the 2nd???


    From Post #55
    Quote Originally Posted by Accessed View Post
    2. I want to make a form that is based off my first query. In my mind it would consist of dependent control boxes. The first would list all of the unique kinases (without duplicates). Once one of the kinases has been selected, you could use a second control box to select from the alleles available for that particular kinase, then select from the available fusion partners and finally the available host cells. Not sure what the proper term would be - maybe cascading dependencies?

    The reason for #2 is I would like to prepare a form where clients could select a specific cell assay using the various options available in qryCellAssays. The form would be more of a running list so that they can sequentially choose the various assays without limit, i.e.

    1. ALK/WT/EML4/BaF3
    2. ALK/WT/TPM3/BaF3
    3. ALK/L1196M/BCR/BAF3

    and so on.
    So there would be a form to enter data into "tblCellFreezerLog" in a specific freezer location. And there would be a combo box bound to field "cellassayFK". Somewhere is the posts I thought I saw something about users would be more comfortable seeing/choosing "ALK/L1196M/BCR/BAF3" rather than the location (cane, box, boxColumn, boxRow).
    Looking at the 2nd relationship pic from Post #81, if you are selecting a cellassay to add to a freezer, how would a specific cell assay be selected? Using a query, the cell assay name (like "ALK/L1196M/BCR/BAF3") could be generated on the fly.

    Quote Originally Posted by orange View Post
    In the tblCellFreezerLog jpg in your #81) cellassayFK is datatype Number. What exactly is presented if you show "ALK/L1196M/BCR/BAF3"?
    The value for tblCellFreezerLog.cellassayFK would be from a query with tblCellAssayLog.cellassayPK (bound field in combo box) and tblConstructs (and other tables), but the combo box displayed value would be something like "ALK/L1196M/BCR/BAF3".

  9. #84
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Thanks Steve.

    OK -- if that's what the OP wants.
    Seems cryptic, but I've seen others.

    Good luck.

  10. #85
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    Greetings all - muddy you say? Indeed I think it was.

    Why the past tense?

    Seems I'm no longer receiving notices of updates to this thread, so I missed all the excitement - but I'm eager to read everything and see if it matches what I chose to do..

    What's say you that - done already we are?

    Mmmm...rather exhausted but the last version posted by Steve held the secret that I eventually de-convoluted. Ladies and Gentlemen...I present Frakenscence (no drumroll required)....

    ACD Assay Database 2016 Distribute.zip

  11. #86
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83

    Progress Update

    For those that haven't found my antics to be tiring, here is the current state of affairs. I have completed the freezer log and tested everything and am pleased so far with performance (still tweaking I must confess). I have also finished my first subform/form (not sure what the correct language is here). This one allows the user to select their kinase of interest and then step through various variables in our panel to further understand how they impact activity.

    Click image for larger version. 

Name:	ActivityCenter.jpg 
Views:	26 
Size:	265.1 KB 
ID:	25068

    I plan to build a similar form that will list assays instead of compounds and allow the user to select their favorite compound (using the combo box) to see how it performs in various assays. I must say that these nested forms/tables are really confusing and for the novice they're not always intuitive. But if you stare at them long enough and try various approaches, eventually one can succeed (but damn frustrating sometimes trying to get there).

    I have one more major project planned that I think will really kick my butt. I need to design a multi-tabbed application that clients can use to select the assays they want us to screen, and ultimately assemble those assays into a virtual experiment that the staff can use to set up the experiments. I have it mapped out in my head, but getting to the end point I think will test my sanity. In it's basic form, the first page will allow the investigator to enter their information (name and company). On the second page, will be a modified version of this form you have already seen earlier (though contained within one of the early databases I shared):

    Click image for larger version. 

Name:	AssayRequest.jpg 
Views:	25 
Size:	95.5 KB 
ID:	25069

    Each entry here would populate an AssayOrder table. When the customer is done entering their selection of compounds and assays, they will be taken to a third page where they can review, confirm and submit their order. The fourth page would be for our eyes only and would be the assay builder. This builder would pull information from each record series and use those data to populate a template much like you see here:

    Click image for larger version. 

Name:	AssayBuilder.jpg 
Views:	25 
Size:	83.0 KB 
ID:	25070

    This was originally designed with Excel and intended to let my staff assemble the "experiments" by simply dragging the merged cells onto the plate - like this:

    Click image for larger version. 

Name:	BuiltAssay.jpg 
Views:	25 
Size:	82.8 KB 
ID:	25071

    But now I'm thinking this can all be done in Access, where I can deploy text boxes to serve as the building blocks. The difference though would be that they can already be "pre-assembled" as shown above because I think there's enough power within access to let me specify what entries go where and what color they should be coded based on their origin (for example customer compound versus reference compound).

    So is there a question in all of this? I think so, namely - would you try to do it with text boxes, with something else, or not try to do it in access at all?

    Thanks for staying awake (again) and paying attention :-)

Page 6 of 6 FirstFirst 123456
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 11-21-2013, 05:06 PM
  2. Need to use constraints
    By tsvetkovdimitar in forum Access
    Replies: 4
    Last Post: 11-19-2013, 01:39 PM
  3. Object dependencies
    By Rob S in forum Access
    Replies: 4
    Last Post: 08-03-2012, 04:00 PM
  4. Query with multiple date range constraints.
    By younggunnaz69 in forum Queries
    Replies: 2
    Last Post: 12-26-2011, 10:45 AM
  5. Replies: 1
    Last Post: 11-07-2011, 11:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums