Assign last record in combo as default

[antonyx]

Me!ComboName.DefaultValue = Me!ComboName.ItemData(0)


this assigns the first value in the combo box to the default.

can this be changed to always assign the last possible record in the list as the default.

 

[antonyx]

also im trying to allow this feature, along with a previous form feature that auto transfers an ID number.. this below code shows the first values in the combo but doesnt create a new record and doesnt transfer the id from the previous form

Option Compare Database
Option Explicit

Private Sub Form_Load()
    Me.Filter = "[Booking_Pickup_ID] = " & Forms!Booking_Pickup![Booking_Pickup_ID]
    Me.FilterOn = True
    Me.[Booking_Pickup_ID].DefaultValue = Forms!Booking_Pickup![Booking_Pickup_ID]
End Sub

Private Sub Form_Current()
If (Me.NewRecord) Then
    Me.bookf = Me!bookf.ItemData(0)
End If
End Sub

 

[antonyx]

once these two features are taken care of i promise i wont need any more help on this issue..

i just need a form to be able to open and auto-populate more then one field with combos



and i need my previous (transfer id from form to form) form feaure to work with the above feature.

 

[Remou]

Please explain a few things, as I can't quite visualize what you are doing. For example, does the previous form (Booking_Pickup?) have a button / code to open this form? If not, how does this form open? If so, can you post the code?
Did you get an error message when this:
[tt] Me.accountchoose = Me!accountchoose.ItemData(0)[/tt]
did not work?
You say that the code "doesnt transfer the id from the previous form", but the code shown looks like it is intended to open a form where a certain Booking_Pickup_ID already exists and filter for this ID. Setting the default value for a control will only work on a new record, however, if you apply a filter to a form which does not return any records, a new record will be displayed. Is this your intention and if so, have you checked the names of the controls are ok?

 

[antonyx]

ok, let me explain..
my main form is here
http://bb.1asphost.com/antonyx6/main.jpg
this form at the moment auto populates the Booker details with the customer 'unknown' details, which is what i want. i also want the account combo (just below the booker) to autopopulate the field with the first record in account which happens to be a '-' (signifying not an account job)

ok so from this form you click pickup.. and then when the pickup form is opened, it automatically links the pickup with the booking id. so here is the main form pickup button

Private Sub Pickup_Click()
On Error GoTo Err_Pickup_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Booking_Pickup"
    
    stLinkCriteria = "[Booking_ID]=" & Me![Booking_ID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Pickup_Click:
    Exit Sub

Err_Pickup_Click:
    MsgBox Err.Description
    Resume Exit_Pickup_Click
    
End Sub

and when the pickup form..
http://bb.1asphost.com/antonyx6/pickup.jpg
is opened it uses this code to filter the incoming booking id.

Private Sub Form_Load()
    Me.Filter = "[Booking_ID] = " & Forms!Booking_Main![Booking_ID]
    Me.FilterOn = True
    Me.[Booking_ID].DefaultValue = Forms!Booking_Main![Booking_ID]
End Sub

now from this pickup, when they click next.. it asks if the user wants to save.. if they click yes then it takes them to another form which lets them assign a customer to that pickup.
this uses the same method.. it automatically transfers the Booking_Pickup ID.. the prob is u have to assign a pickup before you can assign a customer to that pickup..

so basically when the job_pickup_customer form loads. it uses similar code to auto populate the Job Pikcup ID from the pickup form.. it is on this form that i cant combine the ID filter, and the New.Record auto populate combo box..

is this any clearer??

 

[Remou]

This bit:
stLinkCriteria = "[Booking_ID]=" & Me![Booking_ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Already opens the form to a particular record, so this:
Me.Filter = "[Booking_ID] = " & Forms!Booking_Main![Booking_ID]
Me.FilterOn = True
Is not needed, as far as I can see.

It seems to me that the way you have this set up, a pick-up will always have a customer, so the best bet may be to look at the design of your tables. If you are satisfied with this, how about a customer subform on the pick-up form?

Otherwise, why not just duplicate everything from the main form Pickup_Click() to the pick-up form Next_Click() and from the PickUp form load to the Customer assignment form load.

 

[antonyx]

ok but this table structure has to be this way for now, because a pickup can also have multiple customers and then each customer for that pickup may have their own flight number and so on.

ok by removing that code which does the same thing.. i can now open the form with the filter and also auto insert a record.

the code which does that is as follows..

Option Compare Database
Option Explicit

Private Sub Form_Load()
       Me.[Booking_Pickup_ID].DefaultValue = Forms!Booking_Pickup![Booking_Pickup_ID]
End Sub

Private Sub Form_Current()
If (Me.NewRecord) Then
    Me.bookf = Me!bookf.ItemData(0)
End If
End Sub

my only question now is this, how can i set another combo box.. called 'books' also do the same thing on the form current.. is it as easy as this..??

Private Sub Form_Current()
If (Me.NewRecord) Then
    Me.bookf = Me!bookf.ItemData(0)
    Me.books = Me!books.ItemData(0)
End If
End Sub

 

[Remou]

It should be as easy. I guess both books and bookf are similar comboboxes.

 

[Remou]

I have got lost again. If the pick-up form is related by booking id to the main form, how do you assign another customer to this pick-up? It seems from the above that a new pick-up entry is being created in the table for each booking, which further seems to imply for each customer.

 

[antonyx]

ok

BOOKING MAIN -(Booking_ID)->booking pikcup -(Booking_Pickup_ID)->booking pickup customer -(Booking_Pickup_Customer_ID)->booking pickup incoming flight

BOOKING MAIN -(Booking_ID)->booking destination -(Booking_Destination_ID)->booking destination customer -(Booking_Destination_Customer_ID)->booking destination outgoing flight


it goes like that.. it has to go like this because a job may have one or more pickups or destinations, a pickup may have one or more customers, and each customer will have specific flight numbers..

there isnt actually time to reorganise the tables and so on, this way (odd as it may seem) works very well and allows all combinations of jobs to be stored and displayed in reports.

i will try to assign both combos to the record and tell you how i go..

 

[Remou]

Thanks. I was curious.

 

[antonyx]

ok i screwed up one part of it now.. noooo

ok basically the first form loads with the 2 dif combos auto creating a record..

you click pickup, and the pickup form loads fine. you enter a pickup, click next.. if you save it goes to the cust form.

the default customer unknown is already in the record like i want.. you click next, and you click yes and it goes to the inbound flight form. you enter a flight, save and close the flight form..

now the other reason it was created like this is so that my user can from the main form, click on the pickup, and see the pickup for that job (which is already assigned) again if they goto the pickup customer form, they can see the previously entered customer for that pickup..
HOWEVER rite now, the click the inbound flight button, and instead of seeing the previously entered flight details, when it loads it simply adds another record and does not let you see the flight details for that job...

it never used to do this, i mean the old flight is stored in the table when i checked it, but i should appear as the first record for that form when it opens from the respective booking_pickup_customer form.

i will give you the code for my button that opens the flight form and i will give you the code for my flight form..
i cant see any differences here, and have no idea why it is doing this.
the form code for the flight details are

Option Compare Database
Option Explicit

Private Sub Form_Load()
Me.[Booking_Pickup_Customer_ID].DefaultValue = Forms!Booking_Pickup_Customer![Booking_Pickup_Customer_ID]
End Sub

and the button that opens that form (which should pass the id to the flight form, and if a flight exists it should display it, or if its a new flight, it will let you enter it.

Option Compare Database
Option Explicit

Private Sub Form_Load()
Me.[Booking_Pickup_ID].DefaultValue = Forms!Booking_Pickup![Booking_Pickup_ID]
End Sub

Private Sub Form_Current()
If (Me.NewRecord) Then
    Me.bookf = Me!bookf.ItemData(0)
End If
End Sub
[b]
Private Sub close_Click()
Dim Msg As String, Style As Integer, Title As String
Dim stDocName As String
Dim stLinkCriteria As String
   
   Msg = "Do you want to save these details?"
   Style = vbInformation + vbYesNo
   Title = "Save Form Dialog . . ."
   
   If MsgBox(Msg, Style, Title) = vbYes Then
       stDocName = "Booking_Pickup_InFlight"
       stLinkCriteria = "[Booking_Pickup_Customer_ID]=" & Me![Booking_Pickup_Customer_ID]
       DoCmd.OpenForm stDocName, , , stLinkCriteria

       DoCmd.close acForm, "Booking_Pickup_Customer", acSaveYes
   Else
      DoCmd.close acForm, "Booking_Pickup_Customer", acSaveNo
   End If
End Sub[/b]

the button is bolded

 

[antonyx]

ok basically i have 2 identically coded forms.. im sure of it,

both the Booking_Pickup_Customer and the [/b]Booking_Destination_Customer[/b] forms auto assign an unknown customer to the pickup/destination as the default record. Of course it should ONLY do this IF its a new record.

The Destination form realises this.. and if the record has already been made, it simply displays the record.
The Pickup form however, even if its being linked by an already registered record (with the ID), it insists on creating a new Pickup Customer record and doesnt display the previously registered customer.

i will paste the 2 codes which are identical for the forms, but something is up here.. what else could be causing this.

the form that auto adds a record even if its not a new record

Option Compare Database
Option Explicit

Private Sub Form_Load()
Me.[Booking_Pickup_ID].DefaultValue = Forms!Booking_Pickup![Booking_Pickup_ID]
End Sub

Private Sub Form_Current()
If (Me.NewRecord) Then
    Me.bookf = Me!bookf.ItemData(0)
End If
End Sub

Private Sub InFlight_Click()
On Error GoTo Err_InFlight_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Booking_Pickup_InFlight"
    
    stLinkCriteria = "[Booking_Pickup_Customer_ID]=" & Me![Booking_Pickup_Customer_ID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.close acForm, "Booking_Pickup_Customer", acSaveYes

Exit_InFlight_Click:
    Exit Sub

Err_InFlight_Click:
    MsgBox Err.InFlight
    Resume Exit_InFlight_Click
    
End Sub

and identical code that doesnt auto add a record if its not a new one

Option Compare Database
Option Explicit

Private Sub Form_Load()
Me.[Booking_Destination_ID].DefaultValue = Forms!Booking_Destination![Booking_Destination_ID]
End Sub

Private Sub Form_Current()
If (Me.NewRecord) Then
    Me.bookf = Me!bookf.ItemData(0)
End If
End Sub

Private Sub Outflight_Click()
On Error GoTo Err_Outflight_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Booking_Destination_OutFlight"
    
    stLinkCriteria = "[Booking_Destination_Customer_ID]=" & Me![Booking_Destination_Customer_ID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.close acForm, "Booking_Destination_Customer", acSaveYes

Exit_Outflight_Click:
    Exit Sub

Err_Outflight_Click:
    MsgBox Err.Outflight
    Resume Exit_Outflight_Click
    
End Sub

if it has something to do with the previous form button that opens it, then tell me and i will hapilly post the button codes to open the form, which are again identical..

someone please help me

 

[antonyx]

great. i just remade the form from scratch and it worked..

its the old 'restart' solution.

hoorah

 

[Remou]

The acSave arguments of (DoComd) Close are not about saving records, they are about saving changes to the forms. Records are saved by default, so you may need to look at something a little more complicated to prevent this. Depending on your set-up, Undo may suit. You may also wish to check if there are any changes (Me.Dirty, perhaps) before prompting for a save, if the form is to be used to review records.

Is your post of 4 Mar 06 19:24 relevant, or have you gone on past that point?

BOOKING MAIN -(Booking_ID)
->booking pikcup -(Booking_Pickup_ID)
->booking pickup customer -(Booking_Pickup_Customer_ID) [blue]WRONG[/blue]
->booking pickup incoming flight

BOOKING MAIN -(Booking_ID)
->booking destination -(Booking_Destination_ID)
->booking destination customer -(Booking_Destination_Customer_ID) [blue]Right[/blue]
->booking destination outgoing flight

If the above diagram is correct, you will need to post / look at the code on:

->booking pikcup -(Booking_Pickup_ID)

It seems likely that the Booking_Pickup_ID is not going across. It may be worth trying a message box. For example:
[tt]stLinkCriteria = "[Booking_Pickup_Customer_ID]=" & Me![Booking_Pickup_Customer_ID]
MsgBox stLinkCriteria[/tt]

Is it possible that there has been a typo on the form leading to an incorrect field name?

 

[antonyx]

ok, i think i have it resolved now.
the ids pass to and from the forms. and new records are created when the id is not recognised.

 

[Remou]

Take a little time off, now and then.

 

[SBM1745]

Read through this thread, and found the answer I needed. I was doing something similar in my form. I did get one oddity. My form loads and queries a table to populate comboTermFinalCheck.

SELECT tblPAY_EMPLOYEES_Extension_UserCode12.ID, tblPAY_EMPLOYEES_Extension_UserCode12.Description FROM tblPAY_EMPLOYEES_Extension_UserCode12;
*Column Width = 0";1", Bound Column = 1

When a checkbox is clicked, the combobox (which is nested in a tab control) should display the the option that relates to this employee record (the query that the form is based on).

Private Sub chkTerm_Click()
'Set Viewable Tab
    TabActions.Pages(0).Visible = False
    TabActions.Pages(1).Visible = False
    TabActions.Pages(2).Visible = False
    TabActions.Pages(3).Visible = False
    TabActions.Pages(4).Visible = False
    TabActions.Pages(5).Visible = True
    TabActions.Pages(6).Visible = False
'reset click values
    chkHire.Value = 0
    chkRehire.Value = 0
    chkWage.Value = 0
    chkTransfer.Value = 0
    chkLOA.Value = 0
    'chkTerm.Value = -1
    chkOther.Value = 0
'set Page values
    txtTermDate.Value = Date
    txtTermLastDay.Value = [UserCodeT_11]
    chkEligibleRehire.Value = [EligibleForRehire]
    Me.comboTermFinalCheck = Me!comboTermFinalCheck.ItemData([UserCodeL_12] - 1)
End Sub

What I found is that the combo box would display the next option. When [UserCodeL_12] = 3 (Hand Delivered), it would display 4 (Other). I don't know why that is, and subracting 1 fixed it.

Using the above code, has caused a different problem in my report for this form. Using:

=[Forms]![frmByChris_PersonnelActionNotice]![comboTermFinalCheck]

Shows 3 instead of Hand Delivered, even though the combobox is bound to column 1 (the descriptions). I could use some help on this.

-Chris
Starlight B. M.

 

[Remou]

Hi Chris
It is best start a new thread. That way, antonyx will not be troubled by odd emails, and you will get a better range of responses.

 

[SBM1745]

Will do, I just try to keep the forums less cluttered by addressing similar issues in the same thread. Also, I find if others have a problem that relates, when they read through a large thread, they may find the answers they are seeking through reading it.

-Chris
Starlight B. M.