Solved

Autopopulating Fields

Posted on 2004-09-22
9
377 Views
Last Modified: 2008-03-03
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
Comment
Question by:lizzy121202
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 10

Expert Comment

by:perkc
ID: 12127016
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
 
LVL 10

Expert Comment

by:perkc
ID: 12127022
Let me know if you need more clarification.
0
 

Author Comment

by:lizzy121202
ID: 12127110
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 10

Expert Comment

by:perkc
ID: 12127393
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
 
LVL 39

Assisted Solution

by:stevbe
stevbe earned 250 total points
ID: 12128585
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
 

Author Comment

by:lizzy121202
ID: 12132950
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
 

Author Comment

by:lizzy121202
ID: 12133006
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
 

Author Comment

by:lizzy121202
ID: 12133157
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
 
LVL 10

Accepted Solution

by:
perkc earned 250 total points
ID: 12133517
Try:

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

perkc
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question