populating a form based on query conditions

[trixxnixon]

i have a form that is being designed to pend requests in a requests database.
the pend form is opened from an update form used by an employee to enter updates. when the pend form is updated, the tracking number of the current request is imported to the pend form, and query results are displayed at the bottom(all previous pending records for the imported tracking number)
the pend button saves either the start pending date or the end pending date.
when the start date is saved, the file is marked as pended by a check box,
when the end date is saved, the file is marked as not pended by the check box.
i need this form to manage the records based on if there is currently a pending record that is still listed as "pended" by the check box
if there is currently no record for the imported tracking number a new record will be created when the pend button is clicked.
if there is currently an open pending record(record still checked as pended) the form will populate based on the data of the open record.
if there is no open pending record the form will create a new record when the pend button is clicked.

the tracking number has a 1 to many relationship with the pending table and the pending record number is the primary key for the pending table.
Please see the screenshot i have attached if you need help understanding.

 

[jal]

Where exactly are you having a problem?

 

[jal]

You can use DLookup to find out whether the Tracking number is already present in the table. For example let's suppose we wanted to find out whether "Smith" was in the Customers table:


SELECT FirstName FROM Customers WHERE LastName LIKE 'Smith'
which is

Dlookup("FirstName", "Customers", "LastName LIKE 'Smith' ")

The WHERE clause must be set up as to return a single value from the column ColumnName. If the WHERE clause returns zero records (meaning Smith isn't present in the table), the result is Null, which you can detect like this:

Dim var1 As Variant
var1 = DLookup("StudentID", "Students", "Name LIKE 'John' ")
If IsNull(var1) Then MsgBox ("null")

Does that get you started?

 

[jal]

You can also use DLookup to determine the values of the record (if it is present) and then populate the textboxes accordingly.

txtBox1.Value = DLookup.....

To insert a new record, you can do this:

INSERT INTO tblTrackingNumbers (trackNo) VALUES (put tracking number here)

which is something like this:

CurrentDb.Execute "INSERT INTO tblTrackingNumbers (trackNo) VALUES (" & Forms!pendForm!textboxTrack & ")"

 

[trixxnixon]

Where exactly are you having a problem?

i want to set up the form in the picture so that only one pending record can be open at one time. the way to do this is to have the form search the query results for the record that has "yes" selected.

i have a query that returns the only record that is marked as "yes", but the form will not populate with the specific record.

if there is a record that matches the "yes" check box, i want that record to be opened by the form, if there is no record that matches, i want a new record to be opened.

does that make more sense?

 

[jal]

My previous responses seem to point in the right direction. Have you had a chance to try them?

 

[rapsr59]

Hi trixxnixon!

I too want to make sure I understand what you are trying to accomplish.

If I understand you correctly, you have existing records that have a field that is set to true/false (Yes/No). These records are related to a second set of records using a key field.

When the (true/false) field in an existing record is set to true (yes) and is located, you want to populate the main forms’ controls with the records’ data and subsequently you want to populate the sub form with data that is related to the newly populated data in the main form. And you want to accomplish this without setting a record source for the main form.

Additionally, if a record in the main records cannot be located that has the true/false (yes/no) field set to true (yes) you want to set up the main form for a new record.

Is this what you trying to accomplish?

If you are trying to accomplish what I have described then there are programming techniques that will allow you to do this.

Richard

 

[trixxnixon]

Hi trixxnixon!

I too want to make sure I understand what you are trying to accomplish.

If I understand you correctly, you have existing records that have a field that is set to true/false (Yes/No). These records are related to a second set of records using a key field.

When the (true/false) field in an existing record is set to true (yes) and is located, you want to populate the main forms’ controls with the records’ data and subsequently you want to populate the sub form with data that is related to the newly populated data in the main form. And you want to accomplish this without setting a record source for the main form.

Additionally, if a record in the main records cannot be located that has the true/false (yes/no) field set to true (yes) you want to set up the main form for a new record.

Is this what you trying to accomplish?

If you are trying to accomplish what I have described then there are programming techniques that will allow you to do this.

Richard

Correct, this is exactly what i am trying to do.

 

[trixxnixon]

My previous responses seem to point in the right direction. Have you had a chance to try them?

i just got to work, so i am going to try it right now. i will let you know as soon as i get it all in.

 

[trixxnixon]

so this is where i am at so far,

i have been able to use the dlookup to look up the specific record number, how would i be able to get the form to either open up that specific record number, or if there is no matching record, have the form create a new record?

 

[jal]

I don't do much databinding - i find it confusing.

Maybe handle the form_Load event with something like this:

pendForm.AllowFilters = True
pendForm.RecordSource = "SELECT * FROM tblTrackingNumbers"
pendForm.Filter = "TrackingNumber = " & textBoxTrack.Value
pendForm.Requry ???

or maybe forget the filters

pendForm.RecordSource = "SELECT * FROM tblTrackingNumbers WHERE TrackingNumber = " & txtTrack.Value

As I said, I am pretty lost on how to do databinding.