Sub Form Default Value should only be on first record..

[iuianj07]

Hello guys,

I have a form with a subform, the subform is set as continuous form. I have a combo box that lists down different status/phase of a record.

I have setup a default value of "Indexing Request Received" on the sub-form, which should be correct. My question now though is once a record has already been created on the sub-form, and another record (status/phase)needs to be added on the sub-form, how will the default value of the combo box be blank? Currently it is always giving the default value of "Indexing Request Received".

Thank you and let me know if I need to clarify more.

 

[MajP]

There are probably several ways to do it. Not sure what is the easiest for you.
You could run code that as soon as you make a main record it does an insert query to add a new child record.
You could also build your own custom function. The function checks to see if there are any child records for the current parent record. Something like

Public Function getDefault(valIn As Variant) As Variant
strWhere as string
strWhere = "YourField = " & valIn
' or strWhere = "YourField = '" & valIn & "'"
'where val in is the value of the link master field
'Check to see if any child records
if dcount("fieldName","childTableName", strWhere) = 0 then
getDefault = "Indexing Request Received"
end if
End Function

then you could put this in the default property

=getDefault([lnkFieldName])

 

[iuianj07]

Hello MajP,

I apologize, I think I have not explained my problem correctly, advanced apologies again...

Okay, so I have a main form frmJob_TrackingAll which is bound to this SQL:

SELECT Job_Tracking.Situs_ID, Job_Tracking.Project, Job_Tracking.Facility_Number, Job_Tracking.Requested_By, Job_Tracking.Client_Sponsor, Job_Tracking.Property_Loan_Name, Job_Tracking.PortfolioID, Job_Tracking.Date_Requested, Job_Tracking.FileSourceID, Job_Tracking.Specific_File_Instructions, Job_Tracking.File_Completion_Requested_Date, Job_Tracking.Date_Situs_Received_File, Job_Tracking.Situs_Est_Date_Of_Completion, Job_Tracking.PriorityID, Job_Tracking.Link_To_Completed_Files
FROM Job_Tracking
ORDER BY Job_Tracking.Date_Requested DESC;

and I have a subform (set as continuous form) within frmJob_Tracking named frmIndexingLoanStatus (it has the combobox that list all status/phase of each parent record) wherein it is linked via Situs_ID, so whenever the main forms creates a new record, a child record is also being created with the combobox having a default value of 1 (1 is the primary key of "Indexing Request Received").

My question now though is that when we add another child record in the subform, is there anyway that those new records will not have a default value of 1 (Indexing Request Received)? That it will only default to 1 when you first create a child record...


Thank you, and again advanced apologies for not clarifying the issue...

Thank you

 

[MajP]

I think I mentioned this a long time ago.
You currently have code to insert a new child record. If you can post that again it would help, if not I have to look. But basically it is something like

public sub someSub()
some code to run an insert query with jobTracking ID and status = 1
end sub

you need to wrap this code and see if there already exists any records for that job tracking if not run the code. A dcount is an easy way.

public sub someSub()
if dcount("somefield","someChildTable", "Jobtracking ID = " & me.jobtrackingID) < 1 then
some code to run an insert query with jobTracking ID and status = 1
end if
end sub

 

[iuianj07]

Hello MajP,

I think this is the thread you are talking about:

http://www.tek-tips.com/viewthread.cfm?qid=1631675&page=1

Although my question is about when the user is actually using the main form and sub forms? and there is already a child record that has the combo box = 1, and when a user needs to add another child record (new status of the record) that the default value of the combobox should be blank? I think this is more of a design problem, because for example when we look at the sub form, you will see the list of child records that you have already created, and at the very bottom of the record, you will see a blank record of the form (the one that has the asterisk symbol on the left side of each record of the form?) meaning that this record really hasn't been created yet, although it's just that the combobox displays the default value of = 1 ("Indexing Request Received")?

I apologize I might have not been succesful in explaining and describing the problem well, this could be my language barrier problem taking its place...

 

[iuianj07]

no more reply =(