Auto populate field#1 if field#2 is null

Can someone tell me the correct macro condition, item, and expression to populate field # 1 with a word if field # 2 is null and populate field #1 with a different word is field # 2 is not null?

Post your answer or comment

comments powered by Disqus
I am a total access newbie when it comes to designing a database. I have managed to create a simple web database for project management but have run into a snag.

I want to have a field (field 3) auto-populate based on what is entered into two other fields (field 1 & field 2).

Field 1 is a date field (when a presentation is due). Field 2 is a yes/no checkbox (if checked, it means no presentation is necessary). Field 3 would be a calculated text field (?) because text cannot be entered into Field 1.

If field 1 is null and field 2 is checked, then I want "NA" to auto populate into field 3. However, if field 1 is null and field 2 is not checked, I want "TBD" to auto populate into field 3. If field 1 is not null, then I want field 3 to be null.

I hope this makes sense enough for someone to help me out.

How can an edit an auto populated field? After selecting the item description from a drop down combo box, the item code and resin wieght fields are auto poplulated. However, if the item descriptioin is not yet available the user can type in a description of a "new" product. Then this is done the next 2 fields do not auto populated which is what I expect but I want to be able to add the resin weight of the product. I can not do that because the field will not let me type in it. Any help or direction would be VERY much appreciated

I have a form that we are going to use to track outages to our terminals. On the form, the user selects the terminal # and the rest of the information for the terminal auto populates from a query of the terminals table (Terminal name, address, etc). What I am using to auto populate fields is =[Term #].Column(1) for the first field and so on. They then enter the rest of the info (date of outage,etc). The issue I have is that when I look at the table for the outages, the auto populated fields are blank. Is there a way to save this data to the table? Attached Thumbnails     Reply With Quote 09-24-2009, 09:59 AM #2 CGM3 Advanced Beginner Windows XP Access 2003 Join Date Sep 2009 Location Atlanta, GA Posts 46 The problem seems to be that the text fields you are auto-populating are therefore not bound to the corresponding fields in the table to which you wish to save them, so their contents aren't placed in those fields. You need to add VBA code in the Save Record button's Click operation along the lines of:

' make sure the current record is the record you want to save to
rsTable.Fields![Alpha] = txtAlpha.Value

--or possibly--

sSQL = "INSERT INTO [The_Table] ([ID],[Alpha]) VALUES ('" & txtID.Value & "', '" & txtAlpha.Value & "')"
DoCmd.RunSQL (sSQL)

--or whatever prtocol you're comfortable with, in order to explicitly put the values in those text fields into the Access table.

I'm sure this issue has been discussed on this forum many times but I couldn't find the answerwhen I went back through the last couple of weeks in this forum, (or in help). If someone knows the answer to this I would appreciate it!

I have a table for customers. In order to keep the integrity of the data sound, I want the form to be able to auto-populate fields if the name is the same as one previously entered; i.e. You have a returning customer John Sanders, when you start to type the name S-a-n... Access automatically enters in the rest of the name. If the user determines the name that Access supplied is the correct customer they select that entry from the list and the rest of that customers information is automatically updated in the cooresponding fields of the form. If anyone knows the code for this it would be mucho appreciated.


Hi all,

I am new to the forum and also Access novice. I will appreciate very much if someone can help me solve my access problem, I have been trying to find the solution for few days and I finally decided to ask.
I need to subtract one column from another but there are many nulls which I need them as flags. Here is an example:

ID....value1..... value2...... value3
1......608....... null...........500
2......550....... 537.......... 528
3......617....... null........... null
4......698....... 620........... null

The values decrease from filed "value 1" to field "value 3". So I need to subtract Value 2 ( if is not null) from Value 1 and Value 3 (If is not null) from value 2 BUT if value 2 is null than subtract it from Value 1.
In summary I just need the differences between the fields that are not null. If the field is null subtract from next one and so on until you get a not null field.
The differences do not have to be in one column but in 3 in this example. It sounds complicated, I tried using IIf statement but it gets to complicated and there was not enough space to write the whole conditional statement as I have 11 columns to subtract from each other. Can somebody give me any idea of how to solve this and if I need to use SQL how to write it?

Thanks, I appreciate your help

Hi, everyone! I hope that someone is willing to help me with a database that I'm trying to set up. I'm a teacher and want to use Access to track behavior notes and parent contacts. I'm trying to make the database work efficiently for me and need some help with auto-populating fields.

Here's an example of what I want:
Field 1= date (I have this setup to fill in automatically)
Field 2= student name
Field 3= behavior code
Field 4= comment

I want to type in a code for a behavior in Field 3, such as "t", and have "talking excessively" automatically pop up into Field 4. This would just make it faster for me to enter all my info, and then I can make a report that only shows the date, name, and comment without the codes showing up. I currently have the behavior codes listed in another table, but I don't know if that's where they belong or what. I've found some web sites that have codes for auto-populating, but I have no idea where to put them or what to change in order to personalize them for my database. Is there anyone willing to walk me through this? TIA!

These are the codes that I have:
code memo
b did not have book in class
cd class disruption
da detention assigned
dc dress code violation
ds detention served
hd had head down throughout lesson
nw did not complete classwork
o office referral
r referral for 4 tardies
s sleeping
sd skipped detention
t talking excessively
w did not have workbook in class

I have a field on a form that I would like to auto populate with the (concatenated) contents of 3 other fields on the same form. The first 2 fields are drop down boxes and the 3rd is a hand keyed text field.

For example, the first field is Department (4 digit number from drop down box)
Second field is Document Type (3 letter code from drop down box)
Third Field is Document Seq Number (5 digit number hand keyed)

So if I pick 0100 from field1, PCS from field2, and hand key 10000, in my 4th field (called Document ID), I should wind up with 0100PCS10000.

Currently, I have the control source for the Document ID field as
=([Department] & [Document Type] & [Document Seq Number])

In the example above, instead of getting 0100PCS10000, my result is 1110000.

Any help in getting me to the result I am looking for would be greatly appreciated.

Ok, I'm trying to make a database for my wife for our small home business. I have drop down boxes that have items we sell. When you select one, it will auto fill another field with the cost of item we pay. If a customer buys 5 items, 5 fields will auto populate. I want to be able to get the total of those auto populated fields at the bottom so we can see the difference between our cost and what we are charging. The image attached is what I'm talking about. The "Our Cost" column cannot be typed into. I want a total at the bottom of that column. I am trying to use =[OurCost 1]+[OurCost 2]+[OurCost 3]+[OurCost 4]+[OurCost 5]+[OurCost 6]+[OurCost 7]+[OurCost 8]+[OurCost 9]+[OurCost 10] but what it displays is: 4.994.994.994.994.994.99 AND all 10 entries must have a value. I want it to skip empty fields and calculate the total. My table for our costs does have it set to currency.

Here is a link to a screenshot of the results:

Also, same in the Customer Price column. Is there a way I can have it calculate if there isn't a 0.00 in the field? If not, whatever. We can live with that. I just set it to default value = 0

Hi, all. I'm creating a report that shows trainings conducted by employees.

Some of the trainings have names, stored in the field [Training Name]. Others are just categories -- such as Individual Consultation -- and are stored in [EBP Training Category].

In the report, if the [Training Name] field is null, I would like to display the contents of [EBP Training Category] in the same location.

Thoughts on how best to do this? Thanks!


I have two date fields on my form, for when a particular process is suspended and then unsuspended. Some processes will never be suspended, so in these cases I want to disable accidental input to the Unsuspended field.

I thought I could do it like this

	If Me.Study_wide_suspension.Value Is Null Then
    Me.Study_wide_Unsuspension.Enabled = False
    Me.Study_wide_Unsuspension.Enabled = True
End If

but that gives me Run-time Error 424 Object required. This code is on the OnCurrent event by the way. I plan to have similar code on the AfterUpdate event of the Suspension field.

If I use = Null rather than Is Null then I don't get an error message, but it doesn't actually work, i.e. the Unsuspension field is enabled even if the Suspension field hasn't been completed.

I read something somewhere about a date never really being null, so I tried =0 Again, no error, but the disabling doesn't work.

Where am I going wrong here? In case it's relevant, the fields are on a form called "frm01Study" on TabCtl41 (which I never renamed) on a page called pgGlobal which is the second of three tabs.



OK, I have a field (info_updated) on my form (precall_form_view) that I only want to be visible on the form when the value in that field is not null.

I have looked through the forums, and I can not seem to find an example that is like what I am looking for. It may be in there, but sometimes what I call things are not what they are really called....

Here is my code, but when I use it, I never see the field.

I am sure that it is a logic error, and I am too close to see it, but can someone look and advise?


Private Sub Form_Current()
If Info_Updated = Not Null Then
Info_Updated.Visible = True
Info_Updated.Visible = False
End If

End Sub


I wondering if someone can help me as im a complete newbie

Ive upsized a database from access to sql 2000 and i had a bit of code written in vb that once i changed a particular field in a table it automatically inserted todays date into another field, i know it still works if i work with it as an access project but im trying to get completely away from access.


i have a table called tbl_req and a table called tbl_status

in the tbl_req table i have a field called fld_status which is linked to the tbl_status table and it basically contains a unique ID and a string e.g.


unique id - status
1 - completed
2 - cancelled
3 - ordered


what i want to happen is when someone selects a different status in the tbl_req table it automatically inserts todays date in another field called fld_statuschgd in the tbl_req table.



fldstatus - fldstatuschgd
ordered - 12/12/2006

if i now changed from ordered to completed the below should happen

completed - 03/01/2007


ive no idea if i need to use triggers or stored procedures etc.

any help would be most appreciated

many thanks

Brendan Tate

Within my table if Field 1 has an answer of Self (from drop down), then, I would like Fields 6-12 to auto populate; however, if Field 1 does not have an answer of Self, then leave Fields 6-12 blank.

I am not quite sure how to lay this out. I am using Access 2010.

Hi. I tried to find out if this issue was already posted, but did not find. If anyone can help me, I am trying to auto populate two fields on a subform. The field I want to populate is the description of a role. So if the user selects the role name from the combo box, the description field will automatically update. Thanks.

i hope all are fine
i am in huge trouble.
actually i am oracle programer. but my user has asked me to develop a small
program in access. now approximately i have developed the program.
but one problem remaining.
my problem is this i have one table in which i am taking value based on other table this is ok. but i want some value to be inserted automaticaly if i select a code relative name is inserted in the name field.

i have department table in which
deptcode and deptname field is there.

this is matter of situation i know that if i use in this way database is not normalize but this is required.
in item code table i have deptcode and i need daptname aslo.
i can select deptcode from department table. but the requirment is this if i select deptcode the deptname field is auto populated from department table in item code form.
i hope u will understand my problem.
any help should really be appriciated.
thank u and best regards
ur truly rashid zaib


Need help figuring this out. I have a projects table and a contacts table. The projects table has a one to infinity relationship to the contacts.
I have a subform in the project form where you can add contacts to that specific project. I have this setup to auto populate the contact fields based on entering the contact name.

What I'm trying to achieve is if a contact does't currently exist, this subform would also allow you to enter the information without having to go back to the contacts form and enter it.
Currently it will not allow you to do this, i can only get it to pull existing contacts. I don't have the in force integrity checked in the relationship.


I will try and explain this a best possible hopefully it will make sense.
I have a form with 4 text boxes, text1, text2 text3 and text4
I have a button which adds the values in these text boxes to a table. It adds text1 and text2 as a row , then text1 and text3 and finially then text1 and text4 as a row.
Using the following code.

DoCmd.RunSQL "Insert into suppliers(supplier_id,supplier_name) Values (Text1.Value,Text2.Value);"
DoCmd.RunSQL "Insert into suppliers(supplier_id,supplier_name) Values (Text1.Value,Text3.Value);"
DoCmd.RunSQL "Insert into suppliers(supplier_id,supplier_name) Values (Text1.Value,Text4.Value);"

I would like these rows to only be added if the second text box so text2, text3 or text4 have a value. Therefore if text boxes 2 and 4 don't have values only the row with text box 1 and 3 is added to the table.
I don't really know what I am doing now but have tried with If statements and various other methods but had no joy.

hi i am trying to figure out some issues to do with null values of combo boxes for my db. Ive run through multiple different pages and used varying solutions to check for a null value of my combo boxes (ie nothing selected), all failing miserably for me so im thinking there may be an issue with my implimentation or my code in general.
My aim is to check the field qualid,
if it is null have the msgbox pop up saying
New qualification will not be saved due to incomplete recordsNAME. Would you like to proceed?", the yes/no functions leading to two different options
If qualid is not null then check the other fields too, there are 5 fields total. datetype has a default value hence doesnt need to be checked, 1 is a date field(date), 2 combo boxes (qualid, staffid), and a hyperlinked field(trainingrecord).
Any assistance in figuring out my problem/helping correct it would be much apprieciated as ive been scratching my head over this for the few days.

	If IsNull(Me.staffid) Then
     If MsgBox("New qualification will not be saved due to incomplete recordsNAME. Would you like to proceed?", vbYesNo) =
vbYes Then
          MsgBox "Record was not saved", vbOKOnly
          MsgBox "Please select a staff name"
          End If
          If IsNull(Me.qualid) Then
               If MsgBox("New qualification will not be saved due to incomplete recordsQUAL.      Would you like to
proceed?", vbYesNo) = vbYes Then
                    MsgBox "Record was not saved", vbOKOnly
                    MsgBox "Please select a qualification"
                    End If
               If IsNull( Then
                    If MsgBox("New qualification will not be saved due to incomplete recordsDATE. Would you like to
proceed?", vbYesNo) = vbYes Then
                         MsgBox "Record was not saved", vbOKOnly
                         MsgBox "Please select a date"
                    End If
                    If Nz(trainingrecord, "n/a") = "n/a" Then
                         If MsgBox("New qualification will not be saved due to incomplete recordsCERT. Would you like to
proceed?", vbYesNo) = vbYes Then
                              MsgBox "Record was not saved", vbOKOnly
                              MsgBox "Please attatch a training certificate"
                         End If
                    End If
               End If
          End If
     End If


I have to evaluate an is null. i have:

ArrDate = Me.Arrival_Date.Value
TodayDate = Now
If (ArrDate Is Null) Then
'date field is empty
do nothing


'do something

end if

it halts by saying object not found so how do i correctly evaluate the if?


Haven't touched Access in quite some time and now I can do a simple task.

Here's what I have:

2 Tables

Table 1 includes 2 fields COURSE CODE and COURSE NAME

Table 2 will be a list of students and the courses they have taken

What I'm trying to do is as I'm entering a new student in the database I want the course name to automatically fill in when I type in the course name.

I did lookup fields for both the course code and the course name to ensure the correct codes are entered. However since the data in table 2 does not autofill there is the posibilty of having a row with a course name that does not match the course code.

How can I force table to 2 match the course name and code relationship from table 1?

I am trying to modify someones existing Access database that has multiple years worth of data. There are no forms in the database as it is built only with tables and queries (and the few reports that I have been able to add). Inputting data into this database has always been done manually through the various existing tables. One of the main uses for the database is tracking product downloads This is done with a 3 field "Pick-up" table (Customer (First and Last Name (appended from a Contacts table)), Product, and Pickup Date) that is linked to multiple other queries and tables. The issue is the downloads are tracked via e-mail address (also in the Contacts table) so all the data is input manually by polling the e-mails from a separate program and manually inputing each download to the corresponding name in the table.

What I want to do is add an email field to the 'Pick-up" table and have the Customer field auto-populate (again all in a table). Is this possible?

I know it's a silly question, but I'm getting errors. Here's an example mini-table. Id is autonumber, Word is text(100 chrs) and count is number.

Quote: ID Word Count
1 apple 12
2 pear 3
3 nose 56 A loop inside a function takes a word from the functions argument. It uses Dlookup to return the ID of the record if it's already in the table. (Null if it's not there of course.) If it does exist I want to increase the value of the count field for that record. How please?

I am currently using

	rstOut![Count1] = rstOut![Count1] + 1

but this doesn't specifiy what record in the recordset to use and I am getting error 3020, "Update or CancelUpdate without addnew or edit"

My questions are:

1. What syntax do I use to select the record I wish to update as returned by Dlookup, and

2. What syntax for the update of the field?

My current function is below. The passed string can have anything in it, an example may be "ANT HILL ABC ANY OLD TEXT WITHOUT LIMITS". Usually no more than 5 or 6 words but I'm trying to allow for up to 10 with my later handling.

There's other logical problems I see, I never use start position for example, but I've been looking at this for an hour now and I need to take my eyes off it.

Thanks for any advice.

Public Function funFindWords(strTagSet As String) As Integer
'This is the functions return value
funFindWords = 0
'Set this to the start of the string
intStartPos = 1
'get the length of the passed string
intLength = Len(strTagSet)
    'Find the position of the first 'space'
    intSpacePostn = InStr(strTagSet, " ")
    'if none, then all text is one word.
    If intSpacePostn = 0 Then
        strFoundWord = strTagSet
        'Get the word from the full set of tags. The -1 removes the space itself.
        strFoundWord = Left(strTagSet, intSpacePostn - 1)
    End If
    'search rstOut for current instances of this word.
    intTagID = DLookup("ID", "tblTagCount", "[Tag1] = '" & strFoundWord & "'")
    '(Dlookup will return Null if the record does not exist)
    'If it's  Null then it already exists in the table. Just increase the count by 1
    If Not IsNull(intTagID) Then
        rstOut![Count1] = rstOut![Count1] + 1
    'If it IS Null then it doesn't exist in the table. Add a record and update the count
        rstOut![Tag1] = strFoundWord
        rstOut![Count1] = rstOut![Count1] + 1
        intStartPos = intSpacePostn + 1
        strTagSet = Right(strTagSet, (intLength - intSpacePostn))
    End If
    'Set return value, number of words found.
    funFindWords = funFindWords + 1
Loop Until intSpacePostn = 0
End Function

Not sure if this is for the forms thread or tables, think I am in the right place I have 2 tables tblworkdone and tbltests, both have a date field and are both subforms in a tabbed form on the main form. When I enter a date into the tbltests subform I would like the date to automatically be entered into the tblworkdone date field and create a new record so that when I move to the tblworkdone subform with the date already there. Does this make sense. Cheers

I have a query that displays results in a form, but if the query is null, I want to display a different form, or just an error message that says something like "your query returned no results" (right now it will display the form with no fields)

I am a beginning Access/VBA user and have searched and browsed the forum for combinations of null/query/form, but haven't found what I need. Can anyone point me in the right direction?

Thanks for any help.

Not finding an answer? Try a Google search.