Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 392
  • Last Modified:

Autopopulating Fields

I'm unsure if I'm going about this the right way.  I'm creating a tracking database for our returned mail.  There is a three step process for each 'batch'.  Log, Assign, Completed.  I have one form specifically for just logging the batches into the database.  I have another form for changing the Assign and Completed information but neither of these steps can be completed unless there is a logged date.  What I would like is when the batch number is typed in that the logged date will auto-populate.  Any suggestions?
0
lizzy121202
Asked:
lizzy121202
  • 4
  • 4
2 Solutions
 
perkcCommented:
Change the table design for the logged date. Add a default value of the following:

=Date()

or if you also want the time:

=Now()

perkc
0
 
perkcCommented:
Let me know if you need more clarification.
0
 
lizzy121202Author Commented:
Ummm sorry I didn't specify.  I need it to pull the assigned date off the table.  By time it has gone to the second form, it's all ready been logged into the database and thus has a logged date.  When I pull the second form and enter the batch number, I need it to tell me what the logged date is.  
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
perkcCommented:
How are you populating the form? How are you entering the batch number?

Is the form bound to a table or query?

I would probably use a form/subform for this. The main form would have either a textbox or combo-box for the user to enter the batch number, a 'search button', and a subform with the visible property set to 'No'.

When the 'search button' is click I would use a dlookup statement or a recordset to determine if the batch number can be found in the table. If not, display a 'not found' message, requery the subform and make the subform visible.

The subform would have a query for the recordsource using the main form's batch number text/combobox as the critieria.

However, if you already have something else that works just clarify my questions and I'll post another option.

perkc
0
 
stevbeCommented:
in the after_update event of the BAtch Number control ... I am guessing combobox so they can choose batches that have already been entered ... this code assumes that BatchID field is a numeric data type, if it is  a text datatype (in the field's defiunition in your table) then use example 2.

BatchID is a numeric field
Private Sub cboBatchID_AfterUpdate()
    Me.txtBatchDate.Value = DLookup("AssignedDate", "MyTableName", "BatchID=" & me.cboBatchID.Value)
End Sub


BatchID is a text field
Private Sub cboBatchID_AfterUpdate()
    Me.txtBatchDate.Value = DLookup("AssignedDate", "MyTableName", "BatchID='" & me.cboBatchID.Value & "'")
End Sub


Steve
0
 
lizzy121202Author Commented:
The batch number can either be manually entered or scanned in, it will most likely be done by scanner.  We have a product called WASP, it's a bar code scanner with a point click interface.  Wherever the curser is when the barcode is read is where the number will appear.  

The form is bound to table, here are my fields.

BatchNumber       Number   *Primary Key
LogDateTime       Date/Time
Logged               Yes/No
AssignRep
0
 
lizzy121202Author Commented:
Errr...Sorry.
The batch number can either be manually entered or scanned in, it will most likely be done by scanner.  We have a product called WASP, it's a bar code scanner with a point click interface.  Wherever the curser is when the barcode is read is where the number will appear.  

The form is bound to table, here are my fields.

BatchNumber                  Number   *Primary Key
LogDateTime                  Date/Time
Logged                          Yes/No
AssignRep                      Text
AssignDateTime              Date/Time
Assign                           Yes/No
CompletedBy                  Text
CompletedDateTime        Date/Time
Completed                     Yes/No

Currently I have one form that is just for entering the batches into invetory.  I have a second form that is for changing the records.  What I would like is that once the batch number is entered, the logged date autopopulates.  I need this because it will be used as a validation that the records is ready to be assigned out.  It will also be used to determine the service level of the returned mail.  

I've tried using a subform, but I'm not sure how to use them correctly.  I've  created it both ways with these fields as the main form:

AssignRep                      Text
AssignDateTime              Date/Time
Assign                           Yes/No
CompletedBy                  Text
CompletedDateTime        Date/Time
Completed                     Yes/No

and these fields as the subform:

BatchNumber                  Number   *Primary Key
LogDateTime                  Date/Time
Logged                          Yes/No

and vs. versa.  If it would help, I can send you the database.  I'm a self taught Access DB user, so not knowing all the ins and outs, i tend to make things more complicated.  

Thanks
Liz
0
 
lizzy121202Author Commented:
Steve:

If the box is just a numeric text box, would I just drop the cbo at the beginning of the code?  

Me.txtBatchNumber.Value = DLookup("LogDateTime", "BatchTracking" , "BatchNumber=" & me.BatchNumber.Value)

Would this work?  

Liz
0
 
perkcCommented:
Try:

Me.txtLogDateTime = Dlookup("LogDateTime","BatchTracking","BatchNumber=" & Me.BatchNumber.Value)

perkc
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now