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?


Sponsored Links:



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.Edit
rsTable.Fields![Alpha] = txtAlpha.Value
rsTable.Update

--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.

Thanks,
Kevin




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: https://dl.dropbox.com/u/114466/example.PNG

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!




Hi,

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


	Code:
	If Me.Study_wide_suspension.Value Is Null Then
    Me.Study_wide_Unsuspension.Enabled = False
Else
    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.

Thanks,

Pat.




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?

Thanks!

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

End Sub




Hi,

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.

e.g.

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.

tbl_status

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

etc...

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.

e.g.

tbl_req

fldstatus - fldstatuschgd
ordered - 12/12/2006

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

completed - 03/01/2007

etc...

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.




hy
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.
example

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




Hi,

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.

Thanks!




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.



	Code:
	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
          Me.Undo
          MsgBox "Record was not saved", vbOKOnly
          DoCmd.Close
     Else
          MsgBox "Please select a staff name"
          End If
Else
          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
                    Me.Undo
                    MsgBox "Record was not saved", vbOKOnly
                    DoCmd.Close
               Else
                    MsgBox "Please select a qualification"
                    End If
          Else
               If IsNull(Me.date) Then
                    If MsgBox("New qualification will not be saved due to incomplete recordsDATE. Would you like to
proceed?", vbYesNo) = vbYes Then
                         Me.Undo
                         MsgBox "Record was not saved", vbOKOnly
                         DoCmd.Close
                    Else
                         MsgBox "Please select a date"
                    End If
               Else
                    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
                              Me.Undo
                              MsgBox "Record was not saved", vbOKOnly
                              DoCmd.Close
                         Else
                              MsgBox "Please attatch a training certificate"
                         End If
                    Else
                         DoCmd.Close
 
                    End If
               End If
          End If
     End If





Hi

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

else

'do something

end if

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

regards
Peter