Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Mar 2004
    Posts
    287

    Unanswered: DLookup and Back

    Hi Guys,

    I have a problem and can't think of a logical way to solve so I'd thought i would call on you guys to help me..

    I have a basic form that I want to use as a computer layout plan.
    So I had the idea of having a mappos field in the record and on the form, each mappos is linked to a certain part of the form. This works fine!

    My problem is I have the option of a combobox for the computer name, so it can be changed to another computer, hence a new computer is at the point on the desk. This works up to the point of getting the mappos data back to the computer name selected. Eeach area of the form will have numbers, eg: 1-20
    So as you can see from my code, as you open the form it automatically grabs all the appropriate computers and their location from the mappos field in each record.

    So I need to know, that once I've changed the computer name, I need the new name to have its new mappos (eg: just use 1 in this example).

    Any suggestions;


    My code is below for reference if required;
    Code:
    Private Sub Details1_Click()
    DoCmd.OpenForm "AssetMap", acNormal, , "[Name] = [Forms]![MapTS]![Name1]"
    End Sub
    
    Private Sub Form_Open(Cancel As Integer)
    Name1 = DLookup("Name", "TBL_Main", "[MapPos] = 1")
    User1 = DLookup("User", "TBL_Main", "[Name] = [Name1]")
    Model1 = DLookup("Model", "TBL_Main", "[Name] = [Name1]")
    End Sub
    
    Private Sub Name1_AfterUpdate()
    'Grab Name1 and changed its MapPos to 1
    
    
    User1 = DLookup("User", "TBL_Main", "[Name] = [Name1]")
    Model1 = DLookup("Model", "TBL_Main", "[Name] = [Name1]")
        Dim ctlUser, ctlModel As Control
        Set ctlUser = Forms!MapTS!User1
        Set ctlModel = Forms!MapTS!Model1
        ctlUser.Requery
        ctlModel.Requery
    End Sub

  2. #2
    Join Date
    Sep 2006
    Posts
    265
    Personally, I have this information set on the Main Menu. OnOpen is "common" variables are set.

    There is no need to use Dlookup just reference the Main Menu "commons".

  3. #3
    Join Date
    Mar 2004
    Posts
    287
    I don't have a main menu though - it goes straight into the desk map display/layout.

    Each record has the MapPos field, I just need to know how to change that field on the fly using the method above?

    Any idea's?

  4. #4
    Join Date
    Sep 2006
    Posts
    265
    Consider a Main Menu which is automatically opened by the Application.

  5. #5
    Join Date
    Mar 2004
    Posts
    287
    I don't fully understand how that will help me with what i'm trying to do?

  6. #6
    Join Date
    Mar 2004
    Posts
    287
    After reading your post again, I think I should also add that the MapPos is a field on each record so there is no way of having this linked into a Main Menu for my use. (of what I want to do)

  7. #7
    Join Date
    Mar 2004
    Posts
    287
    can anyone help with this please - i'm really stuck now?

  8. #8
    Join Date
    Mar 2004
    Posts
    287
    I need to change the old field as its changed, so if PC1 map location is '1' and I select PC2 as the new computer there, then it will make the maplocations fields to PC1 = 0 (or nothing), and PC2=1
    Get my drift?

    (or even a way to grab the old name (example above would be PC1), change its maploc field and then once the new name is seleccted (eg: PC2), I can use the Afterupdate to set the Maploc field)

    I believe the easiest question to ask at this stage is the following;
    I have used the dlookup to grab a value, how do i change this value and place it back into its original field?!
    Last edited by NeilMansell; 06-25-07 at 06:54.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    <mycontrolname>=DLookup("Name", "TBL_Main", "[MapPos] = 1")
    ..assuming you are using bound controls

    with <myrecordset>
    .<mycolumnname>=DLookup("Name", "TBL_Main", "[MapPos] = 1")
    .update
    end with
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Mar 2004
    Posts
    287
    no i'm using unbound forms. as I have more than one name on the screen/form
    actually there's about 35 of them LOL!!!

    So I have the maploc set on each, and i use dlookup to grab all the info for maploc = 1 and place it on the screen, etc etc

    any suggestions using this method (or alternative methods)

    I lookup the MapPos depending on the name, eg:
    Code:
    MapPos1 = DLookup("MapPos", "TBL_Main", "[Name] = [Name1]")
    MapPos1 = ""
    But how do i change the above code from the selected name1 mappos so that it reneters its mappos to say nothing???
    (Reverse the dlookup?)
    Last edited by NeilMansell; 06-25-07 at 08:04.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    it depends
    you could still set the control value to the appropriate value form your dlookup
    and using your existing code you use to update the underlying dataset, or set an isdirty flag to force the recordset to be updated

    theres the detail of your application ie which mappos to use, or whatever.. thats up to that is your application. if you are changing a mass of controls based on one or more then you need tose tthe approriate vlaues in your applciation

    essentially it doesn't matter waht you do with the product of a dlookup, effectively its a variable that you haven't explicitly declared, you can assign it to anything that will take that value. so you could assign it to a working variable, a control. heck its even possible to assign to vlaues to the same control (say you have a control for the product name, you can also assign the prodcut code to that control as a tag. that way round you have both elements you need (the user wants to see that prodcut ab/986709879-0012 is product <blah>, where as your application deosn't care about the description, beacue all it wants is the product code. so mycontrol.value=<blah product desc>, whereas <mycontrol>.tag=ab/986709879-0012

    you can assign nothing or null to virtually anything (I think some text controls get sniffy handling nulls, but if so set them to "").

    if you are in the unbound world then can do virtually anything. bear in mind though if you have set specific behaviours (eg in form events then there is a risk that these events will fire. if you set some code behind an on change event, or before update event then you have to make sure that code is compatible with what you want to do. its application specific.
    generally speaking I generally tend to find if someone is locked in a tortuous cycle using dlookups and having problems with what values to set, how to reset values, then I tend to think the physical design (of either the data or the form) is suspect.
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Mar 2004
    Posts
    287
    thanks for the explaination. I believe i understand how it works but I'm still confused as to what VB code would i use to push the data back into the dlookup field?

    Woudl you like me to upload an example of what i'm attempting to achieve?

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sorry push data BACK into the lookup field... im not sure I understand

    DLookup, along with its close relatives DCOUNT, DMin, DMax, DSUM (and all the other domain functions) effectively is an all in one SQL query that retrieves a value from the table based on what you specify.

    Im guessing that DMAX generates the same SQL as "select MAX(<myvalue>) from <mytable> where <Myparamtervalues> = <blah>

    its not a magic bullet
    its a one way process, careful use of domain functions is fine, overly liberal use of domain functions can lead to performance degradation.. sometimes significant performance degradation.

    if you want to update a value in a table then you have choices
    ....if its data in the current record then you can do that by changing values locally and then make sure you do an update before discarding that record. if you are using bound controls access will do that automatically for you, if you are not then you need to force your record update process. quite often thats done using a 'isdirty' flag, a flag set when anything in the current record changes.The before any event which would cause access to loose those changes (moving to a new record, inserting a new record, closing the form, closing the database etc) you then save those changes

    if you want to change the value of another record (either int he same table, another table, conceivably another table in another db then you are going to have to use a bit of SQL.. something like "update <mytable> set <mycolumn> = <mynewvalue> where <Myparamtervalue> = <blah>"

    you can change multiple columns to the same row at the same time ....see the UPDATE section int he help file, or on a SQL primer site
    you can change multiple rows at the same time.. so be careful with your where clause.. make sure that the where clause only targets the row(s) that you intend

    update <mytable> set <mycolumn> = <blah> would set every row in that column to <blah>.

    As to how you run that bit of SQl
    you could either apply it to an existing recordset or docmd.runsql

    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Mar 2004
    Posts
    287
    i don't believe i have explained myself fully...

    I have a form with multiple dlookups, each looking at a different record and display each records info (eg: Name, Model, User, etc..) depending on NAME in combobox.
    Want I need to do is change the name in the combobox, which changes the info displayed for that part of the form (That works fine!), and then using the mappos (which is actually the position on the form where the record info is located - literally!), I would like to change the mappos of the selected record on the form (bear in mind that the form is showing multiple records based on the dlookup) to the one selected.

    Is that explained better?

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you have a control with the new value
    you assign that value to the appropriate control

    to do an update you need to be able to clearly identify which record you want to update

    how you update it , as said before depends on your applciation. it is after all a single value you are changing.. the fact that the new value may be coming form a combo box or keyboard or whatever is irrelevant.. what is relevant is how you are currently controlling the way the forms controls are populated.

    if you are using bound controls just change the relevant control, and access will do the rest for you.

    if you are using unbound controls then you need to assign the new value, and do the code behind the scenes to do the actual update.

    in either event you will Im guessing what to change the =vlaue of the control so the user gets direct feedback as to what the row will look like in future.

    If thats not helping then Im guessing its not been explained any better than previous times.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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